Excel XLOOKUP With Multiple Criteria: Formulas & Tips

Formulas & functions · Updated November 19, 2026

To use XLOOKUP with multiple criteria in Excel, multiply the criteria tests to require every condition to be true, then search for 1. For example, with regions in A2:A6, products in B2:B6, names in C2:C6, and the requested region and product in F2 and G2, use =XLOOKUP(1,(A2:A6=F2)*(B2:B6=G2),C2:C6,"Not found"). This returns the name from the first row matching both conditions.

What XLOOKUP does with multiple criteria

XLOOKUP normally compares one lookup value with one lookup array. To match on several fields, create a logical test for each field. Excel evaluates each test as an array of TRUE and FALSE values; multiplying the tests works like AND because TRUE behaves as 1 and FALSE as 0. Only rows where every test is TRUE produce 1, which XLOOKUP can find.

Suppose a small sales list has Region in column A, Product in B, Representative in C, and Sales in D. Its first records are East / Laptop / Ana / 1200, West / Mouse / Ben / 25, East / Mouse / Cara / 30, East / Laptop / Dev / 1350, and West / Laptop / Eli / 1290. If F2 contains East and G2 contains Laptop, the formula in the opening returns Ana: the first row where both requested fields match. The next matching row is Dev, but XLOOKUP returns the first match by default.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The examples here use the standard Excel formula separator, a comma. Some regional settings use semicolons instead; if Excel rejects a formula with commas, check the list separator setting used by your installation.

Build an AND match step by step

Start with one comparison for each criterion. In the example, one test checks whether each region equals the requested region; the other checks whether each product equals the requested product. The multiplication combines the two arrays position by position. A row with East and Laptop produces 1, while a row matching only one of those fields produces 0.

With regions in A2:A6, products in B2:B6, representatives in C2:C6, and the lookup values East and Laptop in F2 and G2, this formula returns Ana:

=XLOOKUP(1,(A2:A6=F2)*(B2:B6=G2),C2:C6,"Not found")

The first argument is the value to find in the calculated match array. The second is that array of combined tests, and the third is the range from which to return a result. The fourth argument supplies a readable result when no row meets both conditions. It is optional, but including it is often clearer than showing Excel’s default not-found error.

For three criteria, multiply in a third comparison. For instance, if a date or status is also part of the lookup, add its comparison in parentheses and multiply it by the other tests. Keep the test ranges the same size and aligned by row with the return range. Mixing a five-row test range with a different-sized return range can produce errors or misleading results.

Return sales or several fields

The return array determines what XLOOKUP gives back. To return sales rather than a representative, point the return array at column D. With regions in A2:A6, products in B2:B6, sales in D2:D6, and criteria in F2 and G2, the example returns 1200:

=XLOOKUP(1,(A2:A6=F2)*(B2:B6=G2),D2:D6,"Not found")

In Microsoft 365 and Excel 2021 or later, the return array can include multiple columns. With the same criteria and data, this returns the representative and sales for the first matching row, so the results spill into two adjacent cells:

=XLOOKUP(1,(A2:A6=F2)*(B2:B6=G2),C2:D6,"Not found")

Make sure the cells where the results need to spill are empty. If another value blocks the output, Excel displays a spill error. A multi-column return is useful when a report needs several details from the matched record, while keeping the criteria logic in one formula.

AND versus OR criteria

Multiplication is for AND: every condition must be met. If the request is instead to match either of two conditions, add the tests and check whether the result is greater than zero. For example, suppose F2 contains East and H2 contains Eli. With region in A2:A6, representative in C2:C6, and product in B2:B6, this returns the product from the first row where the region is East or the representative is Eli:

=XLOOKUP(1,((A2:A6=F2)+(C2:C6=H2)>0),B2:B6,"Not found")

The comparison with zero turns any positive total into TRUE, including a row matching both conditions. Because XLOOKUP searches from the top by default, this formula returns Laptop from the first East record. Be precise about the request: mixing AND and OR logic may need extra parentheses to group the conditions correctly. For example, “East and Laptop, or any record for Eli” is not the same as “East and either Laptop or Eli.”

Use a helper column for a combined key

An alternative is to join the criteria fields into a key in a helper column, then look up the joined criteria. This can be easier to read in a large workbook or when several formulas use the same combination. With region in A2, product in B2, and a helper key in E2, enter this formula in E2 and fill it down through E6:

=A2&"|"&B2

With the resulting keys in E2:E6, representatives in C2:C6, and requested region and product in F2 and G2, this returns Ana:

=XLOOKUP(F2&"|"&G2,E2:E6,C2:C6,"Not found")

The separator helps keep joined values distinct: East joined with Laptop becomes East|Laptop. Choose a separator that cannot appear in the source values, or otherwise account for it, because separator collisions can make different field combinations form the same key. A helper column also adds maintenance: if the source fields change, the keys must update. The direct multiple-test approach avoids that extra column and is often simpler for a small number of conditions.

Common problems and how to fix them

  • No match appears: Check for leading or trailing spaces, inconsistent spelling, and numbers stored as text in one place but as numbers in another. A comparison requires compatible values. Also verify that the lookup cells contain the criteria you intend.
  • The wrong record is returned: There may be duplicate rows matching all criteria. XLOOKUP returns the first match in search order by default, not a random or necessarily unique record. Check duplicates or use a different search strategy if the last match is required.
  • A formula returns an error: Confirm that each criteria range has the same number of rows and that the return range lines up with them. The ranges can be full columns, but formulas over very large ranges may be less efficient than bounded ranges.
  • Results change when copied: Relative references such as F2 and G2 move when a formula is copied. Use absolute references such as $F$2 and $G$2 when the criteria cells should remain fixed. The data ranges may also need dollar signs if the formula is filled elsewhere.
  • Blanks or zeroes look unexpected: A matched row whose return cell is blank may not display the same way as a not-found result. Decide whether a blank return is meaningful, and use an explicit fallback value when a missing match needs to be obvious.

By default, XLOOKUP uses exact matching, which is generally the desired behavior for categories such as region, product, and employee. Matching is not case-sensitive, so East and EAST are treated as equivalent. If capitalization itself distinguishes records, ordinary equality tests will not enforce that distinction; the lookup design needs a case-sensitive comparison instead.

Choosing a formula approach

For most new workbooks, multiplying criteria tests is a good starting point. It keeps each condition visible, handles additional fields without changing the source table, and avoids maintaining a combined key. A helper column can be preferable if many formulas reuse the same composite key, or if a visible key makes the data easier for colleagues to inspect. Consider how the workbook will be maintained, not just which formula is shortest.

Older Excel versions without XLOOKUP may need another lookup approach, such as INDEX and MATCH, or a helper column with a function supported by that version. XLOOKUP is not available in Excel 2019 and earlier. If a workbook must be shared with users on older versions, test compatibility before relying on XLOOKUP or dynamic-array behavior. For Microsoft 365 or Excel 2021 and later, XLOOKUP’s separate lookup and return arrays make the multi-criteria pattern direct and readable.

Frequently Asked Questions

Can XLOOKUP use three or more criteria?

Yes. Add another comparison and multiply it into the match array for each additional condition that must be true. Keep all ranges aligned and of equal size. The formula can become difficult to maintain if it has many conditions, so a helper key or a clearly structured table may be easier to audit.

Does XLOOKUP return the first or last matching row?

It returns the first match by default, searching from the first item toward the last. XLOOKUP also has a search-mode argument that can search in reverse, which returns the last match in a conventional top-to-bottom list. Duplicate matches should be handled intentionally rather than assuming the result is unique.

Can I use cell references for all the criteria?

Yes. Put each requested value in a separate cell and compare the corresponding data column with that cell. This makes the lookup easy to reuse: changing a criterion cell updates the result without editing the formula. Lock the references with dollar signs if copying the formula should not move them.

Why does XLOOKUP show Not found when the values look identical?

Common causes include extra spaces, different data types, small spelling differences, or criteria ranges that do not cover the relevant rows. Text comparisons are not case-sensitive, so capitalization alone is usually not the explanation. Check the underlying values and make sure the test and return ranges are aligned.

Can XLOOKUP return multiple matching rows?

XLOOKUP returns one matched position, although its return array can contain several columns from that row. To return every row meeting conditions in Microsoft 365 or Excel 2021 and later, use a filtering function such as FILTER instead; that is a different task from retrieving the first matching record.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.