Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 93 ยท Lookup and Reference

Lookups with Multiple Criteria

Look up a value in Excel using two or more conditions: helper-column keys, INDEX/MATCH arrays, XLOOKUP, FILTER and the SUMIFS trick, with live examples.

Real tables rarely have one unique column. A price depends on the region and the product. A shift depends on the employee and the date. A tax rate depends on the state and the year. The lookup functions you have met so far take one key, so how do you search by two or three?

Excel gives you several answers: a combined helper key, an array condition inside INDEX/MATCH or XLOOKUP, the FILTER function, and a trick with SUMIFS. Each has a sweet spot and a trap. This lesson shows all five on one small price list.

Excel cannot run on this site. The formulas are genuine Excel formulas; the live examples run a stand-in (a small JavaScript spreadsheet engine for the helper-key and SUMIFS examples, plain JavaScript arrays for the array formulas, and real pandas at the end). They show the logic faithfully, but they are not Excel itself.

Five ways to match two things

Helper key Join the columns into one key.
Array test Multiply conditions inside MATCH.
XLOOKUP Same array, shorter formula.
FILTER Returns every matching row.

Plus the SUMIFS trick for numbers.

1 / 2

Method 1: a helper key column

The oldest and most compatible approach: add a column that joins the criteria into one text key, then look that key up with VLOOKUP (or INDEX/MATCH).

TEXT
A2:  =B2&"|"&C2           (Region and Product joined with a separator)
H2:  =VLOOKUP(F2&"|"&G2, A2:D5, 4, FALSE)

The key column has to be the leftmost column of the VLOOKUP block, which is why the helper sits in column A. With INDEX/MATCH it can be anywhere (INDEX and MATCH). You build the lookup key the same way: F2&"|"&G2.

The separator matters. Without it, different pairs can produce the same key: region A1 with code 2 and region A with code 12 both become A12. Choose a character that never occurs in the data (| is a good one).

The first example runs the helper-key lookup on a small engine, then the SUMIFS trick covered later, and finally shows the no-separator collision:

JS Helper keys, SUMIFS and the missing-separator trap

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice:

  • South|Book found 13, and a combination that does not exist gives #N/A, which you can wrap in IFNA (Handling Errors and Blanks in Formulas).
  • Without a separator, both A1+2 and A+12 become A12, so the lookup returned the first row's rate (5) instead of the intended 9. No error appeared, which is what makes this bug dangerous.

Helper columns are easy to audit and work in every Excel version. Their cost is an extra column to maintain and a lookup key that is text, so numbers inside it (like 1 and 01) must be formatted consistently; see TEXT, VALUE, NUMBERVALUE and Number-as-Text Problems.

Method 2: INDEX and MATCH with an array

You can avoid the helper column by testing both conditions in an array inside MATCH:

TEXT
=INDEX(D2:D6, MATCH(1, (B2:B6=F2) * (C2:C6=G2), 0))

Read it in pieces. (B2:B6=F2) is an array of TRUE/FALSE, one value per row. (C2:C6=G2) is another. Multiplying them turns TRUE/FALSE into 1/0 and keeps a 1 only on rows where both are true. MATCH(1, ..., 0) finds the first 1, and INDEX returns the price at that position. For an or condition you add the arrays instead of multiplying (any value above 0 matches).

In Excel for Microsoft 365 and Excel 2021, this works as an ordinary formula. In older versions it must be entered as an array formula with Ctrl+Shift+Enter, and Excel shows it in curly braces. It also recalculates the whole array on every change, so avoid it on tens of thousands of rows in old versions.

Method 3: XLOOKUP with arrays

XLOOKUP (XLOOKUP and XMATCH) accepts the same arrays and needs no INDEX:

TEXT
=XLOOKUP(1, (B2:B6=F2) * (C2:C6=G2), D2:D6, "no price")

or with a combined key built on the fly, with no helper column:

TEXT
=XLOOKUP(F2&"|"&G2, B2:B6&"|"&C2:C6, D2:D6, "no price")

Both forms return the first match and have the built-in not-found message. This is usually the clearest solution in a modern version of Excel.

Method 4: FILTER

FILTER returns all matching rows (it needs Excel 2021 or Microsoft 365, and the result spills; see Dynamic Arrays and Spilling):

TEXT
=FILTER(D2:D6, (B2:B6=F2) * (C2:C6=G2), "none")

The third argument is what to show when nothing matches. When the combination might occur more than once, FILTER is the honest tool, because a lookup would silently return only the first row. You can wrap it: =TAKE(FILTER(...), 1) for the first, =SUM(FILTER(...)) for a total. More on this in Filtering Data and FILTER, SORT, SORTBY and UNIQUE.

The second example evaluates methods 2 to 4 with plain JavaScript arrays, so you can see the intermediate arrays of ones and zeros that Excel builds. The price list now has a duplicate South/Book row on purpose.

JS Array conditions, XLOOKUP, FILTER and the SUMIFS-style total

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The mask [0,0,0,1,1] shows two rows match. MATCH and XLOOKUP return the first (13); FILTER returns both (13 and 14), and a sum-style total adds both (27). Decide which behavior you want before choosing the function.

Method 5: the SUMIFS trick

SUMIFS adds numbers in rows that meet several conditions. If every combination appears at most once, the sum of a single row is that row's value:

TEXT
=SUMIFS(D2:D5, B2:B5, F2, C2:C5, G2)

It is not strictly a lookup, but it is short, fast, works in every modern version and needs no arrays or helper column (SUMIF, COUNTIF, AVERAGEIF and Their IFS Versions). It has three limits, as the first example showed:

  • It only returns numbers. For text results, use another method.
  • With no match it returns 0, not #N/A. A missing price looks like a real price of zero.
  • With duplicates it adds them together. 13 and 14 would silently give 27.

Use it for quick numeric summaries when you know the pairs are unique, and add COUNTIFS with the same criteria to check that the count is exactly 1.

The same idea in pandas

In pandas the equivalents are a boolean mask with &, a MultiIndex lookup, a merge on several columns for a whole table of keys, and groupby for the SUMIFS idea.

Python Multi-column lookups in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The merge on ["region", "product"] is the table-sized version of the lookups above: it looked up every order at once, and the unmatched East/Pen row got NaN, Excel's #N/A. The groupby sum shows what SUMIFS would have returned for South/Book: 27, not 13.

Choosing a method

Situation Best choice
Must work in old Excel versions helper key + VLOOKUP or INDEX/MATCH
Excel 2021 or 365, single result XLOOKUP with an array condition
More than one row may match FILTER
Numeric result, unique combinations SUMIFS (plus a COUNTIFS check)
Large tables used again and again helper column (computed once, no arrays)

When a lookup returns the first match, ask whether duplicates can exist. If they can, count them: =COUNTIFS(B2:B6, F2, C2:C6, G2) should be 1. A count of 2 means your lookup is guessing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should a helper key such as =B2&C2 use a separator like "|"?

    Plain joining can make two different pairs identical (A12). The lookup returns the first match and gives a wrong value with no error.
  2. 2. In =INDEX(D2:D6, MATCH(1, (B2:B6=F2)*(C2:C6=G2), 0)), what does the multiplication do?

    Each comparison gives an array of TRUE/FALSE. Multiplying turns them into 1/0 and yields 1 only where both are TRUE, so MATCH(1, ..., 0) finds the first row meeting both conditions.
  3. 3. Which function returns EVERY row that meets two conditions, not only the first?

    FILTER returns all matching rows (it spills). VLOOKUP, XLOOKUP and MATCH stop at the first match.
  4. 4. A SUMIFS used as a lookup finds no matching row. What does it return?

    SUMIFS returns 0 when nothing matches, which can be mistaken for a real value of zero. Use COUNTIFS beside it to detect a missing combination.

Summary

  • A helper key (=B2&"|"&C2) turns two criteria into one, and works in every Excel version; always use a separator.
  • INDEX/MATCH or XLOOKUP with (cond1)*(cond2) avoids the helper column; older Excel needs Ctrl+Shift+Enter.
  • FILTER returns all matching rows and takes an "if empty" value.
  • The SUMIFS trick is short but only returns numbers, returns 0 on no match, and sums duplicates.
  • Check for duplicate combinations with COUNTIFS before trusting a "first match" lookup.

Next you will learn to build references that move: OFFSET, INDIRECT and Dynamic References.