Function Problems

How to Return the Last Excel Record by Trade Reference Using XLOOKUP

Muhammad TalhaMuhammad Talha Nov 11, 2026 868 views

Question details

The user needs to retrieve the final MTM value for specific trade references based on their last occurrence in a data table.

How to Return the Last Record by Trade Reference in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell where you want the final MTM record to be displayed.

2
Input the XLOOKUP formula

Enter the formula: =XLOOKUP([@[Trade Ref'#]], [Trade Ref'#], [MTM ], "", 0, -1)

3
Apply the formula

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 XLOOKUP with Bottom-to-Top Search Mode
Understanding the Arguments: The arguments used are: lookup_value, lookup_array, return_array, if_not_found (set to empty ""), match_mode (0 for exact match), and search_mode (-1 for last-to-first).
Advanced Spreadsheet Formulas

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. 1. Open your file in WPS: Launch WPS Spreadsheet and open your financial dataset.
  2. 2. Select the cell: Click on the target cell where you need the MTM valuation to appear.
  3. 3. Enter the XLOOKUP function: Type =XLOOKUP( and select your Trade Ref cell, the lookup column, and the MTM return column.
  4. 4. Set the search mode: Add "", 0, -1 as the final arguments and press Enter to instantly find the bottom-most record.
Fully supports XLOOKUP and advanced dynamic arrays.100% compatibility with Microsoft Excel (.xlsx, .xls) files.Free and lightweight alternative to Microsoft Office.Intuitive tabbed interface for easier large-scale data management.
microsoft office alternative - wps office

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.