How to Return the Last Excel Record by Trade Reference Using XLOOKUP
Question details
The user needs to retrieve the final MTM value for specific trade references based on their last occurrence in a data table.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up the latest valuation record for duplicate trade references in a financial dataset.
- Observed behavior
- Needs a formula to bypass earlier entries and extract the most recent (bottom-most) record for a given trade reference.
Ensure your dataset is arranged chronologically if you want the bottom-most record to represent the latest valuation. Note that the XLOOKUP function requires a newer version of Excel or WPS Office.
Use XLOOKUP with Bottom-to-Top Search Mode
Use the XLOOKUP function and set its search mode argument to -1. This instructs the application to scan the data array from the bottom upwards, returning the last matching record.
XLOOKUP is a highly versatile function that allows you to specify the direction of your search. By altering the search mode, you can easily pull the latest entry in a dataset without sorting.
Click on the cell where you want the final MTM record to be displayed.
Enter the formula: =XLOOKUP([@[Trade Ref'#]], [Trade Ref'#], [MTM ], "", 0, -1)
Press Enter to execute. The -1 at the end of the formula dictates the search mode, forcing the lookup to start from the last row and work upwards until it finds the matching trade reference.

Use the Traditional LOOKUP Function (For Older Versions)
If you are using an older spreadsheet program that does not support XLOOKUP, you can use the traditional LOOKUP function as a workaround.
Quickly Retrieve Last Records Using WPS Spreadsheet
WPS Spreadsheet fully supports advanced modern formulas, including XLOOKUP. You can easily retrieve the last available valuation record for your trade references just like in Microsoft Excel.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your financial dataset.
- 2. Select the cell: Click on the target cell where you need the MTM valuation to appear.
- 3. Enter the XLOOKUP function: Type =XLOOKUP( and select your Trade Ref cell, the lookup column, and the MTM return column.
- 4. Set the search mode: Add "", 0, -1 as the final arguments and press Enter to instantly find the bottom-most record.

Frequently Asked Questions
Why is my XLOOKUP returning the first record instead of the last?
By default, XLOOKUP searches from top to bottom (search mode 1). To return the last record, you must specify -1 in the 6th argument (search_mode) to force it to search from bottom to top.
What happens if the trade reference is not found in the table?
In the formula provided, the fourth argument is set to "" (an empty string), which represents the 'if_not_found' value. If the trade reference is completely missing from the table, the formula will gracefully return a blank cell instead of an error.
Does XLOOKUP require the data to be sorted to find the last record?
No, XLOOKUP does not require the data to be sorted to find the last occurrence. It simply searches the physical rows from the bottom up. However, for the 'last occurrence' to actually represent the 'latest chronological valuation', your data entries should be appended in order.




