Function Problems

How to Use XLOOKUP Reverse Search Mode (Bottom-to-Top)

Amos GikundaAmos Gikunda Oct 8, 2026 870 views

Question details

The user wants to know if the XLOOKUP function supports a reverse search mode that allows searching from the bottom of a range toward the top.

How to Use XLOOKUP Reverse Search Mode
Product
Spreadsheet
Device & OS
not provided
Scenario
Extracting the most recent, latest, or last matching entry in a dataset by searching bottom-to-top.
Observed behavior
Needs to find a specific value by configuring the XLOOKUP formula to evaluate an array starting from the bottom item and moving upward.
Before you start

Ensure you are using a modern spreadsheet version (like WPS Spreadsheet or Excel 365) that supports the XLOOKUP function, as older versions do not include this feature.

Solution 1Recommended

Use the Search Mode Argument in XLOOKUP

Configure the optional search_mode argument of the XLOOKUP function to perform a bottom-to-top reverse search.

The XLOOKUP function includes a built-in search mode argument that controls the direction of the search. By setting this argument to -1, you instruct the formula to start from the last item in the lookup array and search upward. This is particularly useful for finding the latest date or the most recently logged entry in a chronological list.

1
Select a target cell

Click on the blank cell in your worksheet where you want the lookup result to appear.

2
Enter the base formula

Type `=XLOOKUP(` and select the lookup value, followed by the lookup array and the return array, separating each with a comma.

3
Skip optional arguments if needed

The fourth and fifth arguments are 'if_not_found' and 'match_mode'. If you do not need to customize these, simply type commas to skip them until you reach the sixth argument (search_mode).

4
Apply reverse search mode

Enter `-1` as the search_mode argument and close the parenthesis. For example: `=XLOOKUP(A2, B2:B100, C2:C100, "", 0, -1)`. Press Enter to calculate the result.

Use the Search Mode Argument in XLOOKUP
Search Mode Variations: Using -1 searches last-to-first. By default, or if set to 1, XLOOKUP searches first-to-last. You can also use 2 or -2 for binary searches on sorted data.
Perform Advanced Lookups in WPS Spreadsheet

Use XLOOKUP Seamlessly in WPS Office

WPS Spreadsheet fully supports the XLOOKUP function, including reverse search and match modes. You can easily process chronological data and find the most recent entries without needing an expensive software license.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the .xlsx file containing your data.
  2. 2. Start the formula: Select an empty cell and type `=XLOOKUP(` to trigger the function.
  3. 3. Select your arrays: Click and drag to define your lookup_value, lookup_array, and return_array.
  4. 4. Enable reverse search: Enter `-1` as the sixth argument to activate the bottom-to-top search mode.
  5. 5. Execute: Press Enter to instantly fetch the last matching record from your data range.
Fully compatible with Microsoft Excel's XLOOKUP syntaxEasily search bottom-to-top with the -1 search modeLightweight, fast, and completely free to useSeamlessly open, edit, and save .xlsx files without formatting loss
microsoft office alternative - wps office

Frequently Asked Questions

What happens if I don't specify a search mode in XLOOKUP?

If the search mode argument is omitted, the XLOOKUP function defaults to 1. This means it will perform a standard search from the top of the range to the bottom.

Can I use XLOOKUP for a binary search to improve performance?

Yes, you can set the search mode argument to 2 (binary search ascending) or -2 (binary search descending). This drastically improves lookup speed on very large datasets, but requires the data to be sorted properly beforehand.

Why is my XLOOKUP formula returning a #NAME? error?

A #NAME? error usually indicates that your current version of spreadsheet software does not support the XLOOKUP function. You will need to upgrade to a modern version like WPS Office, Microsoft 365, or Excel 2021 to use it.