How to Return the Latest Movement for Each Box in Excel
Question details
The user needs to consolidate box movement data from three different worksheets and extract the most recent date, location, and status for every individual box onto a single summary sheet.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking logistics or inventory movements across multiple status sheets (e.g., Export, Import, Empty) and requiring a master summary of the latest state for each box.
- Observed behavior
- Data is currently scattered across multiple worksheets, making it difficult to manually track the most recent movement without using a dynamic combination and lookup formula.
Ensure you are using a version of Excel that supports dynamic array functions, such as Microsoft 365, Excel 2021, or Excel for the Web. For older versions, alternative methods like Power Query or traditional Ctrl+Shift+Enter array formulas will be required.
Use Dynamic Array Functions (VSTACK, SORT, XLOOKUP)
Combine the source tables into one virtual array, sort it by date in descending order, and use XLOOKUP to find the most recent event for each box.
By utilizing modern Excel functions like VSTACK and EXPAND, you can seamlessly combine multiple ranges and append identifying text. Sorting this virtual array ensures the newest records appear first, allowing XLOOKUP to easily extract the latest status.
Identify the data ranges for your three worksheets. For example, name your ranges 'dateExp', 'dateImp', and 'dateEmpty'.
In the summary worksheet, select the destination cell and enter the following formula: =LET(combined,VSTACK(EXPAND(dateExp,,4,"Exp"),EXPAND(dateImp,,4,"Imp"),EXPAND(dateEmpty,,4,"Empty")),sorted,SORT(combined,2,-1),REDUCE({"Box","Date","Location","Status"},box,LAMBDA(a,b,VSTACK(a,XLOOKUP(b,TAKE(sorted,,1),sorted)))))
Press Enter. The formula will automatically spill the results into the adjacent cells, returning the latest Date, Location, and Status for each Box.

Alternative for Older Excel Versions
For versions older than Excel 2021, use Power Query combined with standard formulas to achieve similar results.
Track Inventory and Movements Effortlessly with WPS Spreadsheet
WPS Office Spreadsheet fully supports dynamic array functions like XLOOKUP, VSTACK, and SORT. This allows you to effortlessly combine tables and extract the latest data without worrying about version compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your logistics or inventory sheets.
- 2. Navigate to the summary sheet: Select the target cell where you want to display the consolidated and latest box movement data.
- 3. Input the array formula: Type in your VSTACK and XLOOKUP formula and press Enter to instantly spill your results.

Frequently Asked Questions
Why does XLOOKUP return the latest movement instead of the first?
By nesting the SORT function and ordering the combined data by date in descending order (newest to oldest), XLOOKUP encounters the most recent date first. Since XLOOKUP defaults to exact match and searches first-to-last, it successfully returns the latest record.
How does the EXPAND function work in this formula context?
The EXPAND function is used to add a new 'Status' column to each individual worksheet's array before combining them. It pads the array to 4 columns, filling the new column with descriptive text like 'Exp', 'Imp', or 'Empty', so you know the origin of the latest movement.
What if I see a #NAME? error when entering the formula?
A #NAME? error typically means your version of Excel does not support one or more of the dynamic array functions used, such as VSTACK, EXPAND, or LAMBDA. You will need to upgrade to Microsoft 365, Excel 2021, or use WPS Office Spreadsheet which supports these modern functions natively.
Do I need to press Ctrl+Shift+Enter for this formula?
No. In modern spreadsheet software that supports dynamic arrays (like Excel 365, Excel 2021, or the latest version of WPS Spreadsheet), you only need to press Enter. The results will automatically spill into adjacent cells.




