How to Use XLOOKUP with Two Alternative Search Fields in Excel & WPS
Question details
The user needs to dynamically retrieve data using XLOOKUP when the search criteria can come from one of two alternative input fields.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Building a lookup interface where users can search by either a primary identifier (like a Part Number) or a secondary identifier (like an Old Number), ensuring the formula automatically searches the correct column based on the populated input.
- Observed behavior
- Using separate standard XLOOKUP formulas for each field causes conflicting results, searches the wrong column, or displays outdated lookup information when input cells are changed.
Ensure that all identifiers in your lookup arrays and input cells have consistent data types, as mixing numbers formatted as text with standard numbers will result in #N/A errors.
Use the LET Function to Dynamically Switch XLOOKUP Inputs
The LET function allows you to define variables for the active search value and lookup column, ensuring XLOOKUP only processes the field that contains data.
By nesting an IF function within LET, you can evaluate which input cell is currently populated and dynamically assign both the search value and the corresponding search column. This completely prevents formula conflicts and keeps your workbook calculation efficient.
Click on the cell where you want the final XLOOKUP result to be displayed.
Type the foundation of the formula: =LET(value, IF(H9<>"", H9, H7), column, IF(H9<>"", A2:A10606, C2:C10606), XLOOKUP(value, column, B2:B10606, "")).
Replace H9 and H7 with your actual alternative input cells. Replace A2:A10606 with the search column for the first input, C2:C10606 with the search column for the second, and B2:B10606 with the column containing the data you want to return.
To prevent users from filling both fields, go to the Data tab, select Data Validation, and apply a Custom formula like =COUNTA($H$7,$H$9)<=1 to the input cells.
Use an IF Statement with Nested XLOOKUPs
If your version of Excel or Spreadsheet does not support the LET function, you can use a standard IF statement to determine which XLOOKUP to run.
Master Complex Lookups in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, LET, and nested logical formulas, allowing you to build dynamic, professional-grade search queries with ease.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your alternative search fields.
- 2. Access the Function Wizard: Select your target output cell and click the 'fx' (Insert Function) icon next to the formula bar.
- 3. Search for your function: Type XLOOKUP or LET in the search box to access the built-in wizard, which guides you through each parameter step-by-step.
- 4. Calculate and verify: Enter your cell references as prompted by the wizard, click OK, and test your alternative search fields to verify the output.
Frequently Asked Questions
Why does my XLOOKUP return #N/A even when the value visually exists in the column?
This error typically occurs due to mismatched data types. For example, if your lookup input is stored as a number, but the values in your lookup array are formatted as text, XLOOKUP will not recognize them as a match. Standardizing the formatting across both fields resolves this issue.
What happens if a user accidentally fills both alternative search fields?
In the IF and LET structures provided, the formula evaluates conditions from left to right. It will process the first true condition (e.g., the H9 cell) and ignore the secondary cell (H7). To prevent user confusion, it is recommended to add Data Validation rules restricting input to only one field.
Can I search across three or more alternative fields using this method?
Yes. You can expand the LET function by nesting additional IF statements within the variable definitions, or use multiple nested IF formulas, evaluating each potential input cell sequentially until a populated one is found.
