Lookup With Multiple Criteria in Excel

A lookup with multiple criteria finds the row where two or more columns all match, such as the price of a Hoodie in size M. Normal VLOOKUP and XLOOKUP only search one column, so you combine the criteria first. This guide shows the modern XLOOKUP method, the INDEX MATCH version for older Excel, a helper column that works everywhere, and when SUMIFS is simpler still.

Why a normal lookup isn't enough#

A normal lookup stops at the first row that matches one value, which is often the wrong row. Here is a price list where the same product appears in several sizes:

F2 holds Hoodie and F3 holds M. =XLOOKUP(F2,A2:A8,C2:C8) returns 30, the price of the first Hoodie row (size S). You need a lookup that checks the product and the size.

How to do a lookup with multiple criteria using XLOOKUP#

Look up the number 1 in an array built by multiplying the tests:

=XLOOKUP(1,(A2:A8=F2)*(B2:B8=F3),C2:C8)

The result is 32. Here is what happens, row by row:

When you multiply TRUE and FALSE values, Excel treats TRUE as 1 and FALSE as 0. Only a row that passes both tests gives 1 × 1 = 1. XLOOKUP then finds the first 1 and returns the price from that row.

Add a third test by multiplying again, for example *(D2:D8="Blue"). Each test must be wrapped in its own brackets, and every range must have the same number of rows. Add the optional fourth argument to show a message instead of #N/A: =XLOOKUP(1,(A2:A8=F2)*(B2:B8="XL"),C2:C8,"Not found") returns Not found. XLOOKUP needs Excel 2021, Microsoft 365 or Excel for the web; the XLOOKUP function guide covers its other arguments.

You can also return more than one column. Make the return array wider, for example =XLOOKUP(1,(A2:A8=F2)*(B2:B8=F3),B2:C8), and the result spills across two cells: the size and the price of the matching row. Leave the cell to the right empty so the result has room.

XLOOKUP with joined keys: another way#

You can also join the criteria into one text key with &, on both sides:

=XLOOKUP(F2&"|"&F3,A2:A8&"|"&B2:B8,C2:C8)

F2&"|"&F3 becomes Hoodie|M, and A2:A8&"|"&B2:B8 builds the same kind of key for every row. The result is again 32.

The | separator matters. Without it, "AB" + "C" and "A" + "BC" would both become "ABC" and could match the wrong row. The multiplication method avoids that problem and also handles conditions such as C2:C8>=32, which joined keys can't, so it's usually the better choice.

How to do it with INDEX and MATCH (older Excel)#

In Excel 2019 and earlier, use the same multiplied tests inside MATCH:

=INDEX(C2:C8,MATCH(1,(A2:A8=F2)*(B2:B8=F3),0))

MATCH finds the position of the first 1 (row 5 of the range), and INDEX returns the 5th price: 32. The 0 at the end of MATCH means exact match.

In Excel 2019 and earlier, this is an array formula: press Ctrl+Shift+Enter instead of Enter (on a Mac, ⌃⇧Return also works). Excel shows curly braces around it in the formula bar. In Excel 2021 and Microsoft 365, a normal Enter is enough. If INDEX MATCH is new to you, start with the INDEX MATCH guide, which explains each part on a single criterion.

How to do it with a helper column (any version)#

A helper column joins the criteria on the sheet, so a plain VLOOKUP can search it.

Step 1: Add a key column on the left#

Insert a column before the table, or use a spare column at the left. In it, type =A2&"|"&B2 and fill down. Each row now has a key such as Hoodie|M.

Step 2: Look up the joined value#

If the key is in column A and the price is now in column D, use:

=VLOOKUP(F2&"|"&F3,A2:D8,4,FALSE)

This works in every version of Excel, and anyone can see how it works by looking at the key column. The cost is an extra column to maintain.

The SUMIFS shortcut for numbers#

If the value you want is a number, and each combination appears only once, SUMIFS gives the same answer without any array logic:

=SUMIFS(C2:C8,A2:A8,F2,B2:B8,F3)

The result is 32. Be careful, though: if a combination appears twice, SUMIFS adds both prices instead of returning one, and if there's no match it returns 0 instead of an error. Use it only when you're sure the combinations are unique.

Common mistakes in multiple-criteria lookups#

• Using AND instead of *. AND(A2:A8=F2,B2:B8=F3) returns one TRUE or FALSE for the whole range, so the lookup fails.
• Forgetting the brackets around each test. A2:A8=F2*B2:B8=F3 is calculated in the wrong order.
• Ranges of different sizes, such as A2:A8 and B2:B9, which return #VALUE!.
• Numbers stored as text, so a test like A2:A8=101 finds nothing. Make both sides the same type.
• Expecting every match. These formulas return the first matching row only. To list all matches, see return every match for a lookup with FILTER. To get the most recent match instead of the first, see find the last match with XLOOKUP.

Multiple-criteria lookups are a common step up in interviews and data tests, after a basic VLOOKUP; see the Excel interview questions and the Excel skills for data analysts.

Practise it#

Try the live exercise on this page to find the Hoodie M price. Then practise INDEX and MATCH in a live spreadsheet to see how position-based lookups work, and use the XLOOKUP lesson to get quick with the modern syntax before combining criteria.