XLOOKUP only matches against a single column out of the box, but real lookups often need to match on two or three things at once: a SKU plus a region, a date plus a customer, an employee plus a project. This tutorial walks through every reliable way to do multi-criteria XLOOKUP in Google Sheets, with the trade-offs spelled out so you can pick the right pattern for your sheet.

The helper-column approach (simplest, fastest)

If you control the lookup sheet, the cleanest pattern is to add a single helper column that concatenates the criteria, then look up the concatenation: =A2&"|"&B2 in column D of the source sheet, and =XLOOKUP(F2&"|"&G2, Source!D:Z, 23, FALSE) on the lookup side. The pipe character avoids accidental collisions like "AB"+"C" matching "A"+"BC". This approach is dramatically faster than array tricks once your sheet has thousands of rows.

INDEX/MATCH with array criteria (no helper column needed)

When you cannot or do not want to add a helper column, INDEX with a boolean MATCH does the same job inline: =INDEX(ReturnRange, MATCH(1, (Crit1Range=Crit1)*(Crit2Range=Crit2), 0)). The two equality checks each return an array of TRUE/FALSE, multiplying coerces them to 1/0, and MATCH finds the first row where both conditions are satisfied. Confirm with Ctrl+Shift+Enter on legacy XLOOKUP, or just press Enter in modern Google Sheets — array evaluation is now the default for these patterns.

FILTER for the cleanest expression

If you only need the value (not the row index), FILTER(ReturnRange, Crit1Range=Crit1, Crit2Range=Crit2) reads almost like English. It returns every row that matches, so wrap it in INDEX(..., 1) if you only want the first match, or use it as-is when there are legitimately multiple results. FILTER also returns #N/A when nothing matches, which you can absorb with IFNA the same way you would with XLOOKUP.

Worked example: pricing across region and tier

A pricing table has columns Region, Tier, Price. You want the price for the row matching both a region in cell A2 and a tier in B2. Helper-column version: in the pricing sheet add =A2&"|"&B2 as column D, then on the lookup side write =XLOOKUP($A2&"|"&$B2, Pricing!D:E, 2, FALSE). INDEX/MATCH version on the same data: =INDEX(Pricing!C:C, MATCH(1, (Pricing!A:A=$A2)*(Pricing!B:B=$B2), 0)). FILTER version: =IFERROR(INDEX(FILTER(Pricing!C:C, Pricing!A:A=$A2, Pricing!B:B=$B2), 1), "No match"). Pick whichever your team finds most readable.

Worked examples

Two-criteria lookup with helper column

=XLOOKUP(A2&"|"&B2, Source!D:F, 3, FALSE)

Add the helper column on the source side: =A2&"|"&B2.

INDEX/MATCH array-style, no helper column

=INDEX(Source!C:C, MATCH(1, (Source!A:A=A2)*(Source!B:B=B2), 0))

Returns the first row where both equalities are true.

FILTER for legibility

=IFERROR(INDEX(FILTER(Source!C:C, Source!A:A=A2, Source!B:B=B2), 1), "")

Reads top-to-bottom and is the easiest to debug when something goes wrong.

Common mistakes (and how to avoid them)

  • Concatenating criteria without a separator: "A"&"BC" collides with "AB"&"C". Always include a delimiter your data does not contain.
  • Forgetting that array-style INDEX/MATCH treats every row in the column — referencing whole columns (A:A) on a 100k-row sheet will be slow.
  • Mixing absolute and relative references when copying the formula down. Lock the criteria ranges with $ so they don't drift.
  • Treating an empty criterion as "match anything". Empty cells equal empty cells, not every cell — handle the wildcard case explicitly.

Tips that pay off later

  • If you need wildcards as well as multiple criteria, switch to QUERY with a WHERE clause that uses LIKE.
  • Wrap repeated multi-criteria lookups across the same return column in ARRAYFORMULA to avoid copy-pasting one cell at a time.
  • Profile with the formula evaluator (Tools → Formula evaluator in newer builds) before you blame Sheets — most slowness comes from referencing entire columns instead of bounded ranges.

When to reach for something else

If your lookup logic involves more than three criteria, or any range comparisons, you will be much happier in QUERY than chaining INDEX/MATCH together.

Functions used in this tutorial

XLOOKUP → INDEX → MATCH → FILTER → QUERY →