Home › Excel functions › VLOOKUP › Multiple criteria
VLOOKUP with Multiple Criteria in Excel
VLOOKUP looks for one value. But often one value isn't enough: you want the price of a hoodie in size M, or the sales of one rep in one region. There are three ways to look up with two or more criteria, and the classic one still uses VLOOKUP.
VLOOKUP · In this guide
Why one criterion isn't enough
Take this price list. The product name appears three times, once for each size:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Key | Product | Size | Price |
| 2 | T-shirt-S | T-shirt | S | $12.00 |
| 3 | T-shirt-M | T-shirt | M | $13.00 |
| 4 | T-shirt-L | T-shirt | L | $14.00 |
| 5 | Hoodie-S | Hoodie | S | $32.00 |
| 6 | Hoodie-M | Hoodie | M | $34.00 |
| 7 | Hoodie-L | Hoodie | L | $36.00 |
A VLOOKUP for "Hoodie" in column B stops at the first match and always returns the price of size S. To get size M you need both conditions at once. That's what column A is for.
Method 1: a helper column with VLOOKUP
The trick is to turn two conditions into one value. In a new first column, join the product and the size with the & operator:
=B2&"-"&C2Copy it down and every row gets a unique key, like Hoodie-M. The dash in between prevents mix-ups: without it, "AB" + "C" and "A" + "BC" would both become "ABC".
Now build the same key inside VLOOKUP. With the product in F2 and the size in G2:
=VLOOKUP(F2&"-"&G2, A2:D7, 4, FALSE)F2&"-"&G2 becomes "Hoodie-M", VLOOKUP finds that key in column A and returns column 4: $34.00. This works in every version of Excel, which is why it's still the most common method.
The key column must be the first column of your range. VLOOKUP only searches the leftmost column, so put the helper column at the left of your table, or start the range at the helper column.
Practice: VLOOKUP with two criteria
The helper column is already there. Build the key in the formula with & and look up the price. The tips tell you what to type next.
Exercise: in cell H2, write =VLOOKUP(F2&"-"&G2,A2:D7,4,FALSE) to find the price. Turn on JavaScript to try it interactively.
Method 2: XLOOKUP with multiple criteria
In Excel 365 and Excel 2021 you don't need a helper column. XLOOKUP can test several conditions at once:
=XLOOKUP(1, (B2:B7=F2)*(C2:C7=G2), D2:D7)Each comparison gives a list of TRUE and FALSE values. Multiplying them gives 1 only in the row where both are TRUE, and XLOOKUP looks for that 1. Add a third condition by multiplying another comparison, such as (E2:E7=H2).
Method 3: INDEX and MATCH
In older versions without XLOOKUP, INDEX and MATCH do the same with an array formula:
=INDEX(D2:D7, MATCH(1, (B2:B7=F2)*(C2:C7=G2), 0))In Excel 2019 and earlier, confirm this formula with Ctrl + Shift + Enter instead of Enter. Excel then shows it between curly braces.
Which method should you use?
| Method | Works in | Good to know |
|---|---|---|
| Helper column + VLOOKUP | Every version | Easy to read and check, but adds a column to your table. |
| XLOOKUP | Excel 365, 2021 and later | No extra column, easy to add more conditions. |
| INDEX/MATCH | Every version | No extra column, but harder to read; older versions need Ctrl + Shift + Enter. |
If the file is shared with people on older Excel versions, the helper column is the safe choice. Otherwise XLOOKUP is the cleanest.
Watch out for duplicates. If two rows match all your criteria, every method returns only the first one. To get all matches, use FILTER instead.
More VLOOKUP guides
Frequently asked questions
Can VLOOKUP use two criteria?
Not directly, because VLOOKUP looks for one value. Join both criteria into one key with &, in a helper column and in the lookup value, and VLOOKUP can find it.
How do I add a third criterion?
Add it to the key: =B2&"-"&C2&"-"&D2 in the helper column, and the same three cells joined with dashes in the formula. With XLOOKUP, multiply one more comparison.
Why does my VLOOKUP with multiple criteria return #N/A?
The key in the formula doesn't exactly match the key in the helper column. Check that both use the same separator, in the same order, and that there are no extra spaces in the source cells.
Is SUMIFS an alternative?
For numbers, yes. If every combination appears only once, SUMIFS with two conditions returns the same number as a lookup. It returns 0 instead of an error when there's no match, and it can't return text.