How to Use XLOOKUP Reverse Search Mode (Bottom-to-Top)
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.

- 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.
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.
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.
Click on the blank cell in your worksheet where you want the lookup result to appear.
Type `=XLOOKUP(` and select the lookup value, followed by the lookup array and the return array, separating each with a comma.
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).
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 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. Open your dataset: Launch WPS Spreadsheet and open the .xlsx file containing your data.
- 2. Start the formula: Select an empty cell and type `=XLOOKUP(` to trigger the function.
- 3. Select your arrays: Click and drag to define your lookup_value, lookup_array, and return_array.
- 4. Enable reverse search: Enter `-1` as the sixth argument to activate the bottom-to-top search mode.
- 5. Execute: Press Enter to instantly fetch the last matching record from your data range.

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.




