How to Use XLOOKUP with Multiple Lookup Columns in WPS Spreadsheet
Question details
The user needs to retrieve a specific value by searching for a search term across two different columns (e.g., a Home Team or Away Team column) and returning a result from the corresponding data column.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up statistical data where the search term may appear in one of several possible columns depending on the event configuration.
- Observed behavior
- Because a standard XLOOKUP only searches one array, the user wants to implement a formula that acts as a fallback to search a second array if the primary search fails.
Verify that your lookup arrays (e.g., Home Team, Away Team) and return arrays (e.g., Home Defense, Away Defense) are precisely the same size to prevent calculation errors.
Use Nested XLOOKUP Functions
By placing a second XLOOKUP inside the [if_not_found] argument of the first XLOOKUP, you can sequentially search multiple columns.
The XLOOKUP function features a built-in 'if_not_found' parameter. Instead of returning a static text error when a match isn't found, this argument can hold a completely new XLOOKUP function, creating a sequential lookup chain.
Click on the cell where you want the final percentage or lookup result to be displayed.
Start by typing the formula for the first column: =XLOOKUP(A3, [Home Team Range], [Home Defense Percentage Range]). Do not close the parentheses yet.
Add a comma to move to the 'if_not_found' argument, and type the second lookup: XLOOKUP(A3, [Away Team Range], [Away Defense Percentage Range]).
Combine the logic and close the parentheses. The final formula will look like this: =XLOOKUP(A3, [Home Team], [Home Defense Percentage], XLOOKUP(A3, [Away Team], [Away Defense Percentage])). Press Enter to apply.
Easily Handle Complex Data Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced, modern array functions like XLOOKUP. This allows you to perform complex, cross-column searches and dynamic data retrieval without complex legacy formula combinations.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your lookup data.
- 2. Start your formula: Click on your result cell, type =XLOOKUP( to activate the formula tooltip, and select your primary search value.
- 3. Select ranges effortlessly: Use your mouse to quickly drag and highlight your Home and Away arrays directly from the sheet.
- 4. Apply and fill: Press Enter to execute the nested formula, then drag the fill handle down to apply the lookup chain to the rest of your dataset.
Frequently Asked Questions
Can I nest more than two XLOOKUP functions for three or more columns?
Yes, you can continue nesting additional XLOOKUP functions inside the 'if_not_found' argument of each subsequent lookup. This allows you to chain as many columns as needed.
What happens if the lookup value isn't found in any of the nested columns?
If the final nested XLOOKUP also fails to find a match, the formula will return an #N/A error by default. To prevent this, you can add a final text string (like "Not Found") at the end of the last XLOOKUP's 'if_not_found' argument.
Why is my nested XLOOKUP returning a #VALUE! error?
A #VALUE! error usually occurs if your lookup array and return array are not the exact same size. Ensure that the ranges for [Home Team] and [Home Defense Percentage] cover the exact same number of rows.
