Function Problems

How to Use Excel XLOOKUP with Multiple Criteria

WPS Editor Sep 28, 2026 871 views

Question details

The user needs to retrieve a value from a table using an Excel formula based on multiple criteria, including a combination of row values and a column selection, while retaining the existing table layout.

How to Use XLOOKUP in Excel for Multiple Criteria

Product: Excel
Device & OS: not provided
Scenario: Looking up specific data based on complex, combined criteria across rows and columns without altering the visual structure of the spreadsheet.
Observed behavior: To retrieve the correct value dynamically, a nested formula combining XLOOKUP, SCAN, and LAMBDA is required. Unsupported versions will return a #NAME? error.

Before you start

Ensure you are using a recent version of Excel (such as Microsoft 365 or Excel 2021) that supports dynamic array functions, as older versions do not recognize XLOOKUP, SCAN, or LAMBDA.

Solution 1Recommended

Use a Nested XLOOKUP, SCAN, and LAMBDA Formula

Apply a powerful combination of dynamic array formulas to look up values based on multiple row and column criteria without altering your table layout.

When dealing with tables designed for readability (e.g., with blank cells meant to inherit the value from above), standard lookups fail. By nesting SCAN and LAMBDA within your XLOOKUP, you can virtually fill in these blank cells in the lookup array dynamically.

You can combine multiple lookup criteria by concatenating them with the ampersand (&) operator.

Select the target cell

Click on the cell where you want the final lookup result to be displayed (for example, cell D13).

Enter the nested formula

Type the following formula: =XLOOKUP(A13&B13,SCAN(0,A2:A7,LAMBDA(a,i,IF(i="",a,i)))&B2:B7,XLOOKUP(C13,C1:G1,C2:G7))

Apply the calculation

Press the Enter key. The formula will calculate the arrays dynamically and return the exact value matching your multiple criteria.

Use a Nested XLOOKUP, SCAN, and LAMBDA Formula

Troubleshooting #NAME? Errors: If Excel returns a #NAME? error, verify that your version supports XLOOKUP, SCAN, and LAMBDA. Additionally, ensure your regional settings use commas as list separators; otherwise, replace the commas in the formula with semicolons.

Advanced Formula Alternative

Use WPS Spreadsheet for Advanced Multi-Criteria Lookups

WPS Office Spreadsheet provides robust support for modern array formulas, allowing you to execute multi-criteria lookups with functions like XLOOKUP effortlessly. It is fully equipped for professional data analysis.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data table.
2. Select the output cell: Click the cell where the lookup result should appear.
3. Enter the XLOOKUP formula: Type your XLOOKUP formula, using the ampersand (&) operator to combine your lookup values and arrays.
4. Retrieve your data: Press Enter to execute the formula and accurately pull the targeted data based on your combined criteria.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsNatively supports advanced lookup functions to solve multi-criteria scenariosLightweight, fast, and free to useFamiliar user interface for immediate productivity and seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return a #NAME? error?

The #NAME? error usually occurs when the spreadsheet software does not recognize a function. This happens if you are using an older version of Excel (like 2016 or 2019) that does not support XLOOKUP, SCAN, or LAMBDA. It can also occur if there is a typo in the function name.

Can I use XLOOKUP with multiple criteria without creating helper columns?

Yes, you can evaluate multiple criteria directly inside the XLOOKUP function by concatenating the lookup values (e.g., Value1&Value2) and their corresponding lookup arrays (e.g., Array1&Array2) using the ampersand (&) operator.

What is the purpose of SCAN and LAMBDA in this multi-criteria formula?

SCAN and LAMBDA are used here to create a virtual array that temporarily fills in blank cells within a column (commonly seen in tables formatted for visual readability). This ensures that every row has a definitive value to evaluate against your criteria without permanently altering the table layout.

Excel & Data Problems