How to Use Excel XLOOKUP with Multiple Criteria and a Dynamic Return Column
Question details
The user needs to construct a two-dimensional XLOOKUP formula in Excel to search for a specific item based on multiple attributes and retrieve data from a column that can change dynamically.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching a complex dataset where multiple conditions (like product type, color, and size) must be met, and the return column is not fixed but determined by another lookup value (like inventory status).
- Observed behavior
- The goal is to successfully build a nested XLOOKUP formula utilizing Boolean criteria arrays for the rows and a secondary XLOOKUP for the columns to achieve accurate dynamic lookups.
Ensure your dataset is organized in a clear tabular format without merged cells, and verify that your version of Excel or WPS Spreadsheet supports the modern XLOOKUP function.
Use a Nested XLOOKUP Formula with Boolean Logic
Combine Boolean arrays to evaluate multiple conditions simultaneously, and nest a second XLOOKUP inside the return array argument to dynamically select the correct column.
XLOOKUP can seamlessly handle multiple conditions by multiplying Boolean arrays together. When combined with a nested XLOOKUP that fetches the return array, it creates a powerful two-dimensional lookup.
Multiplying the criteria arrays creates an internal array of 1s and 0s. A value of 1 represents a row that meets all specified conditions, which is why the primary lookup value is set to 1.
Set the primary lookup value to 1. In the lookup array argument, multiply your criteria together using parentheses. For example: (K3=E5:E17)*(L3=F5:F17)*(M3=G5:G17).
Instead of providing a static range for the return array, insert a second XLOOKUP. Use this nested formula to search your header row for the target column name, like XLOOKUP("In Stock",E4:I4,E5:I17,"N",0).
Put everything together into a single formula: =XLOOKUP(1,(K3=E5:E17)*(L3=F5:F17)*(M3=G5:G17),XLOOKUP("In Stock",E4:I4,E5:I17,"N",0)).
Press Enter to execute the formula. Adjust the cell references and ranges as needed to perfectly match your worksheet's layout.
Easily Manage Advanced Formulas with WPS Spreadsheet
WPS Office provides full support for modern array functions like XLOOKUP, making it easy to perform multiple-criteria lookups, two-dimensional searches, and dynamic data analysis exactly as you would in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing data file.
- 2. Initiate the XLOOKUP function: Select the cell where you want the result to appear and type =XLOOKUP( to display the formula tooltip.
- 3. Input the criteria arrays: Enter 1 as the lookup value, then multiply your criteria ranges together, such as (A2:A10=G1)*(B2:B10=G2).
- 4. Nest the return array and apply: Type the secondary XLOOKUP for the return array argument, close your parentheses, and press Enter to calculate the dynamic result.
Frequently Asked Questions
Why does my XLOOKUP with multiple criteria return a #VALUE! error?
This error most commonly occurs if the ranges for your criteria do not match in size. Ensure that all lookup ranges (e.g., E5:E17, F5:F17) have the exact same number of rows. Even a one-row discrepancy will cause the Boolean array calculation to fail.
Can I use INDEX and MATCH instead of XLOOKUP for multiple criteria?
Yes, you can achieve the same result using an INDEX and MATCH combination with array logic (e.g., =INDEX(C2:C10, MATCH(1, (A2:A10=E1)*(B2:B10=E2), 0))). However, XLOOKUP is generally easier to read, inherently handles dynamic arrays, and doesn't require Ctrl+Shift+Enter in older environments.
What if no match is found for the criteria in XLOOKUP?
XLOOKUP features a built-in 'if_not_found' argument. You can add a custom message or value at the end of your primary XLOOKUP formula, such as "Item not found", which will display automatically if the Boolean arrays do not generate a match.
