How to Use XLOOKUP to Find a Value from Two Columns in Excel
Question details
The user needs to retrieve a value using a search key that might be located in either column F or G, and wants the formula to return a blank cell rather than an #N/A error if no match is found.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up mapping data (such as a region) from another worksheet when the source site identifier could be entered in one of two different columns.
- Observed behavior
- A standard single XLOOKUP returns an #N/A error if the exact value isn't found in the designated lookup array. The desired behavior is to check a primary column, fall back to a secondary column if needed, and gracefully return a blank if both fail.
Ensure your version of Excel or WPS Spreadsheet supports the XLOOKUP function, which is available in Microsoft 365, Excel 2021, and recent versions of WPS Office.
Use a Nested XLOOKUP Formula
Leverage the built-in 'if_not_found' argument of XLOOKUP to automatically trigger a second search if the first one yields no results.
The XLOOKUP function takes up to six arguments. The fourth argument is '[if_not_found]'. By placing a completely new XLOOKUP formula inside this fourth argument, you instruct the spreadsheet to perform a secondary search in a different column whenever the primary search fails.
To prevent an #N/A error when neither column contains the value, you can place an empty string ("") as the fallback for the second, nested XLOOKUP.
Click on the cell where you want the mapped region or lookup result to appear.
Begin your formula by pointing to the first possible input cell (e.g., F2) and setting up the lookup and return arrays: =XLOOKUP(F2,'Other Tab'!$B$2:$B$1000,'Other Tab'!$C$2:$C$1000,
Instead of closing the formula, add a second XLOOKUP as the fourth argument to check the alternative cell (e.g., G2): XLOOKUP(G2,'Other Tab'!$B$2:$B$1000,'Other Tab'!$C$2:$C$1000,""))
Press Enter to execute the complete formula. Once it calculates correctly, click the fill handle in the bottom-right corner of the cell and drag it down to apply the formula to the rest of the column.
Use WPS Spreadsheet to Handle Complex XLOOKUPs
WPS Spreadsheet natively supports modern functions like XLOOKUP, allowing you to perform dynamic lookups across multiple columns and worksheets with ease.
- 1. Open your workbook: Launch WPS Spreadsheet and open your .xlsx file containing the mapping tables.
- 2. Enter the nested formula: Type the nested XLOOKUP formula directly into the target cell, utilizing the 'if_not_found' parameter for dual-column searches.
- 3. Calculate and evaluate: Press Enter to execute the formula. WPS Spreadsheet will instantly process the dual-column search and cleanly handle unmatched data.
Frequently Asked Questions
What happens if I try to do this with VLOOKUP instead of XLOOKUP?
VLOOKUP does not have a built-in 'if_not_found' parameter. To achieve the same result with VLOOKUP, you would need to use an IFERROR wrapper containing two separate VLOOKUPs (e.g., =IFERROR(VLOOKUP(...), IFERROR(VLOOKUP(...), ""))), making the formula much longer and harder to troubleshoot.
Can I search more than two columns using this nested method?
Yes. You can continue nesting additional XLOOKUP functions inside the 'if_not_found' argument for as many columns as you need to check. However, if you are checking many columns, an INDEX/MATCH array formula or Power Query might be more efficient to maintain.
Why is my XLOOKUP formula returning a #NAME? error?
The #NAME? error indicates that your spreadsheet application does not recognize the XLOOKUP function. This usually happens if you are using an older version of Excel (like Excel 2016 or 2019). You need Excel 365, Excel 2021, or the latest version of WPS Office to use this function.
