How to Use XLOOKUP with Multiple Criteria and a TRUE or FALSE Flag
Question details
The user needs to retrieve a specific SKU by matching both an item number and a boolean (TRUE/FALSE) condition from separate columns.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up a unique item SKU when a single item number has multiple variants differentiated by a TRUE or FALSE value in a secondary column.
- Observed behavior
- A single-criteria lookup is insufficient to distinguish the variants, requiring a multi-criteria array formula to correctly identify the target row.
Ensure that your item numbers and TRUE/FALSE flags are formatted consistently in their respective columns, and verify that your spreadsheet application supports the XLOOKUP function or array formulas.
Use XLOOKUP with Boolean Array Multiplication
The most efficient way to look up values with multiple conditions is by multiplying condition arrays inside the XLOOKUP function.
By multiplying two condition arrays together (such as (E:E=E16)*(F:F=F16)), you create an array of 1s and 0s. The XLOOKUP function then searches for the value '1' to locate the exact row where both the item number and the TRUE/FALSE flag match.
Click on the cell where you want the resulting SKU to be displayed.
Type =XLOOKUP(1, (E:E=E16)*(F:F=F16), G:G, "Not found") into the formula bar. Replace E, F, and G with your specific Item Number, Boolean Flag, and SKU columns.
Press Enter to calculate the formula. The corresponding SKU will be returned from column G if both conditions are met.
Use INDEX and MATCH with Nested IF Statements
If your spreadsheet software does not support XLOOKUP, use a combination of INDEX, MATCH, and nested IF functions to process multiple criteria.
Master Complex Lookups effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like XLOOKUP, allowing you to easily manage multi-criteria data analysis just as you would in Microsoft Excel.
- 1. Download and Install: Get WPS Office from the official website and follow the installation prompts to set it up on your device.
- 2. Open your Data File: Launch WPS Spreadsheet and open your existing workbook containing the lookup data.
- 3. Apply the Formula: Use the formula bar to easily insert XLOOKUP or INDEX/MATCH functions and resolve your multiple criteria queries instantly.
Frequently Asked Questions
Why does my multi-criteria XLOOKUP return a #VALUE! error?
A #VALUE! or #N/A error usually occurs if the array ranges do not match in size. Ensure all ranges in your formula (e.g., E:E, F:F, and G:G) have the exact same number of rows.
Can I add a third condition to my XLOOKUP formula?
Yes, you can add as many conditions as needed by multiplying another array in the lookup array section. For example: =XLOOKUP(1, (Range1=Condition1)*(Range2=Condition2)*(Range3=Condition3), ReturnRange).
Do I always need to use Ctrl+Shift+Enter for the INDEX/MATCH solution?
In older software versions, the nested IF approach inside MATCH requires Ctrl+Shift+Enter to evaluate as an array formula. Modern versions with Dynamic Arrays (including recent updates of WPS Spreadsheet) often handle it by just pressing Enter.
