Lookup formulas

XLOOKUP with Multiple Criteria in Excel and Google Sheets

The fastest Excel pattern is to search for 1 across several TRUE/FALSE tests multiplied together. In Google Sheets, FILTER is often cleaner when you have more than one condition.

=XLOOKUP(1,(A2:A10=G2)*(B2:B10=H2),C2:C10,"Not found")

Sample data

Imagine a small order table where the same customer can place more than one order. A normal XLOOKUP by customer name is not enough because it may return the first matching row instead of the exact order you need.

If G2 contains Nova Co and H2 contains East, the formula should return Pro.

Excel: XLOOKUP with two criteria

=XLOOKUP(1,(A2:A5=G2)*(B2:B5=H2),C2:C5,"Not found")

Here is what each part does:

• A2:A5=G2 checks the customer column.
• B2:B5=H2 checks the region column.
• Multiplying the tests turns rows that match both conditions into 1.
• XLOOKUP searches for 1 and returns the plan from C2:C5.

Excel: add a third condition

Add another TRUE/FALSE test inside the lookup array.

=XLOOKUP(1,(A2:A20=G2)*(B2:B20=H2)*(C2:C20=I2),D2:D20,"Not found")

This pattern is useful when a row is only unique after checking customer, region, and plan.

Safer option: helper column

For shared workbooks, a helper column is often easier to audit. In E2, combine the fields that make each row unique:

=A2&"|"&B2

Then use a simple XLOOKUP:

=XLOOKUP(G2&"|"&H2,E2:E20,C2:C20,"Not found")

This is less elegant, but it is easier for teammates to understand and debug.

Google Sheets: use FILTER for multiple conditions

Google Sheets has XLOOKUP, but FILTER is usually clearer for multiple conditions because it accepts each condition separately.

=IFERROR(FILTER(C2:C20,A2:A20=G2,B2:B20=H2),"Not found")

If more than one row matches, FILTER can return multiple rows. If you only want the first match, wrap it with INDEX:

=IFERROR(INDEX(FILTER(C2:C20,A2:A20=G2,B2:B20=H2),1),"Not found")

Common errors

#N/A

No row matched every condition. Check spelling, extra spaces, date formats, and whether numbers are stored as text.

#VALUE!

Your lookup tests may not be the same height. For example, do not mix A2:A20 with B2:B200 in the same XLOOKUP test.

Wrong result

The formula returns the first matching row. If several rows share the same criteria, add another condition or use a helper column that creates a truly unique key.

Which method should you use?

Official references

For syntax details, see Microsoft Support's XLOOKUP function reference and Google Docs Editors Help's XLOOKUP function page.

Related spreadsheet fixes