AI Excel Generator
← ALL GUIDES

How to Use XLOOKUP with Multiple Criteria in Excel

Updated November 4, 2026 · AI Excel Generator

To use XLOOKUP with multiple criteria, combine the conditions into one lookup array and search for a value that represents all of them.

This guide shows practical formulas for matching records across several columns, with examples you can adapt to your worksheet.

How XLOOKUP handles multiple criteria

XLOOKUP normally searches one lookup array for one lookup value.

To add criteria, create a calculation that evaluates each row against every condition.

In Excel, multiplying TRUE/FALSE tests coerces them to 1 and 0: a row that satisfies all conditions produces 1, while any row that fails produces 0.

XLOOKUP can then search for 1.

Assume a worksheet contains departments in A2:A100, employee names in B2:B100, and monthly sales in C2:C100.

Cells E2 and F2 contain the department and employee to find.

Use:

=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"No match")

The formula returns the sales amount for the first row where both the department and employee match.

If the matching row has sales of 4200, the expected result is 4200.

If there is no row matching both criteria, it returns No match.

Build the formula step by step

  1. Compare the first field. A2:A100=E2 creates a TRUE/FALSE result for each department.
  2. Compare the second field. B2:B100=F2 creates another set of TRUE/FALSE results.
  3. Combine the tests. Multiplication produces 1 only when both tests are TRUE for the same row.
  4. Return the requested data. The return array, C2:C100, contains the values XLOOKUP returns from that matching row.

Every range in this formula has the same number of rows.

Keep the lookup arrays and return array aligned; otherwise, the formula can return an error or an unintended result.

Add a third or fourth criterion

For more conditions, multiply another comparison into the lookup array.

Suppose column D contains regions and cell G2 contains the requested region:

=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2)*(D2:D100=G2),C2:C100,"No match")

This returns sales only when the department, employee, and region all match.

Add each additional condition as another parenthesized test.

Use the same row bounds for every test and for the return range.

Return multiple columns from the matched row

If you want to return several adjacent fields, make the return array span those columns.

For example, if C contains sales, D contains region, and E contains a status, the formula can return all three values from the matched row:

=XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2),C2:E100,"No match")

In versions of Excel that support dynamic arrays, the results spill into neighboring cells.

Make sure those cells are empty.

In older Excel versions without dynamic arrays, select the output cells first and enter the formula as an array formula if required by that version.

Use OR logic instead of requiring every condition

Multiplication represents AND logic: every condition must be true.

For OR logic, add the tests and check whether the result is greater than zero.

This example returns the value from column C for the first row where the department matches E2 or the employee matches F2:

=XLOOKUP(1,((A2:A100=E2)+(B2:B100=F2))>0,C2:C100,"No match")

The comparison to 0 converts the combined values into TRUE/FALSE results.

XLOOKUP returns the first row that satisfies either condition.

If you need a mix of AND and OR logic, group the tests with parentheses and check the resulting expression carefully.

Choose which match XLOOKUP returns

When multiple rows satisfy the conditions, XLOOKUP returns the first match by default, searching from the top.

To return the last matching row, set the search mode to -1, the fifth argument:

=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"No match",0,-1)

The 0 specifies exact match mode, which is also the default.

The final -1 tells XLOOKUP to search from the last item to the first.

This is useful when records are ordered by date and you want the most recent matching entry, provided the newest record appears last.

Match text criteria without extra spaces

Unexpected spaces can prevent a match.

If source values may contain leading or trailing spaces, clean the source data when possible.

Alternatively, apply TRIM to the compared values, but using text functions over entire ranges may affect performance in a large workbook.

Check for inconsistent punctuation, number formats, and dates too: a number stored as text may not match the same number stored as a numeric value.

For a case-sensitive match, ordinary equality tests are not sufficient because Excel comparisons are generally case-insensitive.

Use EXACT for the relevant text condition:

=XLOOKUP(1,EXACT(A2:A100,E2)*(B2:B100=F2),C2:C100,"No match")

This formula requires the department text to match capitalization as well as characters, while the employee comparison remains case-insensitive.

Excel and Google Sheets compatibility

XLOOKUP is available in Microsoft 365 and newer perpetual editions such as Excel 2021 and Excel 2024.

It is not available in Excel 2019 or Excel 2016.

In an unsupported Excel version, use INDEX and MATCH or another compatible lookup approach.

Google Sheets also supports XLOOKUP.

The same multiple-criteria pattern generally works with equal-sized ranges, though function availability and argument behavior can depend on the spreadsheet product and locale.

Some locales use semicolons instead of commas between function arguments.

If Sheets does not recognize the function, check its current function documentation and your spreadsheet locale settings.

Common errors to check

  • #N/A: No row satisfies all criteria, or a value differs because of spaces, spelling, data type, or date formatting.

    The fourth argument can provide a clearer fallback message.
  • #VALUE! or unexpected results: Confirm that all criteria ranges and the return range cover the same rows and that the criteria expression uses valid array operations.
  • Wrong row returned: There may be duplicate matches.

    XLOOKUP returns the first one unless you choose reverse search mode.
  • #SPILL!: A multi-column result may be blocked by existing content in the cells where it needs to spill.
  • Slow calculation: Avoid full-column array calculations across many conditions in a large workbook.

    Use bounded ranges or an Excel Table with structured references.

For a reusable workbook, consider converting the data into an Excel Table and replacing fixed ranges with table columns.

This keeps the formula tied to named fields and lets the ranges expand as records are added.