How to Use XLOOKUP with Multiple Criteria in Excel

1. XLOOKUP Basics

XLOOKUP replaces the older VLOOKUP, HLOOKUP and LOOKUP functions. It is available in Excel 365 and Excel 2021 (or later). The syntax is simple and eliminates many of the quirks of the legacy functions.

Syntax

XLOOKUP(lookup_value, lookup_array, return_array,
        [if_not_found], [match_mode], [search_mode])

Simple Example

=XLOOKUP("Banana", A2:A4, B2:B4, "Not found")

Result: 0.30

The function looks for “Banana” in column A and returns the matching price from column B. If “Banana” is missing, it returns “Not found”.

Why XLOOKUP Beats VLOOKUP

These basics are the foundation for the multi‑criteria techniques that follow.

2. Why Multiple Criteria Matter

A single key (e.g., an employee ID) works only when that column is unique. In real‑world data, rows are often identified by more than one attribute. Using only one column can give the wrong result, return duplicates, or force you to create a helper column that clutters the sheet.

Benefits of Multi‑Criteria Lookups

1. Eliminate accidental duplicates – The combination of two (or more) fields is far more likely to be unique.
2. Future‑proof data – When new rows are added, the composite key stays unique without extra maintenance.
3. Avoid hidden helper columns – Traditional workarounds concatenate fields in a hidden column; using XLOOKUP with multiple criteria keeps the logic inside the formula.

3. Helper‑Column Method (Works in All Recent Excel Versions)

The most compatible way to perform a multi‑criteria lookup is to create a helper column that concatenates the criteria, then let XLOOKUP work on that single column.

Step‑by‑step

1. Insert a helper column next to your source table.
2. Concatenate the fields you need, using a delimiter that never appears in the data (e.g., |).
3. Build the same concatenated string for the lookup value.
4. Use a regular XLOOKUP on the helper column.

Sample Data

Helper column formula (D2, copy down)

=A2 & "|" & B2

Lookup formula – sales for Region = “East” and Product = “Banana”

=XLOOKUP(
    "East|Banana",   // lookup_value
    D2:D5,           // lookup_array (helper)
    C2:C5,           // return_array (Sales)
    "Not found",     // if_not_found
    0,               // exact match
    1                // first‑to‑last
)

Result: 430

Things to watch for

4. Boolean-Array Method: No Helper Column (Excel 365/2021)

You can skip the helper column by letting XLOOKUP search an array of 1s and 0s. Multiply one test per criterion: a row scores 1 only when all tests are TRUE, and XLOOKUP finds the first 1.

General Pattern

=XLOOKUP(1, (range1=criteria1)*(range2=criteria2)*(range3=criteria3), return_range, "Not found")

Practical Example

Goal: Sales for ID 101, Region East, Month Feb.

=XLOOKUP(1, (A2:A6=101)*(B2:B6="East")*(C2:C6="Feb"), D2:D6, "Not found")

Result: 1300

Why it works: each comparison returns an array of TRUE/FALSE. Multiplying them gives 1 only on row 4 (ID 101, East, Feb), so XLOOKUP(1, ...) returns the matching value from D2:D6. The lookup array and the return array are both 5 rows tall, which XLOOKUP requires.

Return all matches instead of the first one

If more than one row can match, use FILTER, which returns every matching row:

=FILTER(D2:D6, (A2:A6=101)*(B2:B6="East"))

With the example data this spills 1200 and 1300.

Tips & Pitfalls

5. Common Pitfalls and How to Avoid Them

6. FAQ

How does XLOOKUP differ from INDEX/MATCH for multiple criteria?
INDEX/MATCH can handle multiple criteria only with array formulas (e.g., MATCH(1,(A:A=val1)*(B:B=val2),0)), which require Ctrl+Shift+Enter in older Excel. XLOOKUP with a helper column or with a Boolean array provides a more readable, non‑array solution and works natively in Excel 365/2021.

Can I use XLOOKUP with a single helper column?
Yes. Concatenate the needed fields in one helper column (e.g., =A2 & "|" & B2) and use that column as the lookup_array. This works in all versions that support XLOOKUP, including Excel 2019 with a helper column.

What is the best way to handle errors in XLOOKUP?
Supply the optional if_not_found argument ("Not found" or 0). For more complex handling, wrap the formula in IFERROR or IFNA to trigger alternative calculations.

Is there a limit to the number of criteria in XLOOKUP?
The function itself only accepts one lookup value, so you need a helper column or FILTER. With FILTER, you can combine as many Boolean conditions as you like, limited only by Excel’s overall array‑size constraints (≈ 1 048 576 rows).

How do I perform a case-sensitive XLOOKUP?
XLOOKUP ignores case by default. Search an EXACT test array for TRUE:

=XLOOKUP(TRUE, EXACT(lookup_array, lookup_value), return_array, "Not found")

Bottom line – Whether you prefer a permanent helper column for maximum compatibility or the dynamic FILTER approach for newer Excel versions, mastering these patterns lets you retrieve exactly the row you need without fragile work‑arounds. Happy looking up!