Lookup with Multiple Criteria

Excel Formulas › Lookup

Excel 365Legacy altTwo keys

When one column isn’t enough to identify a row — you need the price for a specific product in a specific size — you do a two-criteria lookup. The trick is to join the keys together, either inside XLOOKUP or with INDEX/MATCH.

Quick formula: to find the row where product (A) is G2 and size (B) is G3:
=XLOOKUP(G2&"|"&G3, A2:A8&"|"&B2:B8, C2:C8)
Concatenate both keys on each side with a separator so the pair is matched as one value.

Functions used (tap for the full reference guide):

XLOOKUPINDEXMATCHSUMIFS

The example

A price list keyed by product + size. We want the Widget / Large price.

ABC
1ProductSizePrice
2WidgetSmall$10
3WidgetLarge$18
4GadgetSmall$12
5GadgetLarge$20

The formula

The price where product = Widget and size = Large:

=XLOOKUP("Widget"&"|"&"Large", A2:A5&"|"&B2:B5, C2:C5) // → $18

How it works

The lookup matches a combined key:

  1. On the right, A2:A5&"|"&B2:B5 builds a temporary key column: "Widget|Small", "Widget|Large", …
  2. On the left, "Widget"&"|"&"Large" builds the same kind of key from your two criteria.
  3. XLOOKUP finds the one combined key that matches and returns the price beside it.
  4. The "|" separator prevents false matches (so "ab"+"c" can’t collide with "a"+"bc").

If the result is a number you can add, SUMIFS is simplest. =SUMIFS(C2:C5, A2:A5, G2, B2:B5, G3) returns the price directly — no concatenation — as long as each product+size pair is unique.

Try it: interactive demo

Live demo

Choose a product and size; the combined-key lookup finds the price.

Price:

Variations

Legacy INDEX/MATCH (array-entered)

Excel 2019 and older — confirm with Ctrl+Shift+Enter:

=INDEX(C2:C5, MATCH(G2&"|"&G3, A2:A5&"|"&B2:B5, 0))

Numeric result: use SUMIFS

If the answer is a number and the key is unique:

=SUMIFS(C2:C5, A2:A5, G2, B2:B5, G3)

Three or more criteria

Just chain more keys with the same separator:

=XLOOKUP(G2&"|"&G3&"|"&G4, A2:A5&"|"&B2:B5&"|"&D2:D5, C2:C5)

Pitfalls & errors

#N/A. The combined key didn’t match — usually a stray space or different capitalization in one column. Wrap each side in TRIM, and remember XLOOKUP isn’t case-sensitive.

Always use a separator. Without one, "12"+"3" and "1"+"23" both become "123" and match wrongly. A "|" (or any character not in your data) prevents this.

Legacy INDEX/MATCH needs array entry. The concatenated-range version must be entered with Ctrl+Shift+Enter in Excel 2019 and older.

Practice workbook

📊
Download the free Lookup with Multiple Criteria practice workbook
The price list with a live two-key lookup (SUMIFS), the XLOOKUP and INDEX/MATCH concatenation methods, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I look up a value with two criteria in Excel?
Concatenate the keys with a separator inside XLOOKUP: =XLOOKUP(G2&"|"&G3, A2:A8&"|"&B2:B8, C2:C8). If the result is a unique number, =SUMIFS(value, k1, c1, k2, c2) is even simpler.
How do I do a multi-criteria lookup without XLOOKUP?
Use INDEX with a concatenated MATCH: =INDEX(C2:C5, MATCH(G2&"|"&G3, A2:A5&"|"&B2:B5, 0)), entered with Ctrl+Shift+Enter in Excel 2019 and older.
Why use a separator like | between keys?
It prevents false matches where different key pairs concatenate to the same string, e.g. "12"+"3" vs "1"+"23". Use any character that never appears in your data.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Two-way lookup · Get the most recent match · Tax-bracket lookup

Function references: XLOOKUP · INDEX · MATCH · SUMIFS