How to Fix XLOOKUP Match Mode and Search Mode Disabled in Excel
Question details
The user cannot find or use the Match mode and Search mode options for the XLOOKUP function in the Formula Builder.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to apply advanced optional arguments like match_mode and search_mode to an XLOOKUP function using the graphical Formula Builder.
- Observed behavior
- The Formula Builder only exposes the mandatory arguments and the exact-match option, leaving the match_mode and search_mode arguments unavailable or hidden.
Ensure your spreadsheet software is updated to a modern version that fully supports dynamic arrays and the XLOOKUP function, such as Microsoft 365, Excel 2021, or the latest version of WPS Office.
Enter the Complete XLOOKUP Formula Manually in the Formula Bar
Bypass the limitations of the graphical Formula Builder by typing the complete XLOOKUP syntax directly into the formula bar.
The XLOOKUP syntax contains six arguments: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). By default, the function performs an exact match and searches first-to-last. Because the last three arguments are optional, some versions of the Formula Builder interface may not display them to keep the UI clean.
Click the cell where you want the XLOOKUP function's result to appear.
Click inside the Formula Bar at the top of your worksheet to begin typing.
Type `=XLOOKUP(` followed by your lookup_value, lookup_array, and return_array, separating each with a comma.
Type another comma to enter the [if_not_found] argument (or leave it blank between two commas). Then, type your desired [match_mode] (e.g., -1 for exact match or next smaller item) and [search_mode] (e.g., -1 for last-to-first search).
Close the parentheses and press Enter. The formula will calculate using the advanced modes even though they were hidden in the builder.
Update Your Spreadsheet Software
Ensure you are running the latest version of Excel, as earlier implementations might have UI limitations in the Formula Builder.
Master Advanced XLOOKUP Arguments Easily with WPS Spreadsheet
WPS Spreadsheet provides full support for the modern XLOOKUP function, including intuitive formula tooltips that guide you through optional arguments like match_mode and search_mode without hiding them.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to perform the lookup.
- 2. Start the XLOOKUP function: Select an empty cell and begin typing `=XLOOKUP(`.
- 3. Follow the formula prompt: Use the interactive tooltip to input your lookup_value, lookup_array, and return_array. Continue typing commas to effortlessly reach the match_mode and search_mode arguments.
- 4. Apply and calculate: Press Enter to instantly execute the function and retrieve your matched data.
Frequently Asked Questions
What are the default values for match_mode and search_mode in XLOOKUP?
By default, the match_mode is set to 0, which means it requires an exact match. If an exact match is not found, it returns an #N/A error. The default search_mode is 1, which searches the array from the first item to the last item.
Why doesn't the Formula Builder show all XLOOKUP arguments?
The graphical Formula Builder in some versions of spreadsheet software prioritizes displaying mandatory arguments to keep the interface simple for beginner users. Because match_mode and search_mode are optional, they may be hidden, requiring you to type them directly into the formula bar.
How do I do a reverse search (bottom-up) using XLOOKUP?
To perform a reverse search, you need to use the search_mode argument. If you want the default exact match, simply skip the match_mode argument with an extra comma, and then type -1 for the search_mode. For example: =XLOOKUP(A1, B:B, C:C, "Not Found", , -1).
Is XLOOKUP available in all versions of Excel?
No. XLOOKUP was introduced in Microsoft 365 and is also available in Excel 2021 and modern alternatives like WPS Office. Older versions, such as Excel 2016 or 2019, do not support the XLOOKUP function.
