VLOOKUP with multiple criteria, using a helper column
VLOOKUP only takes one lookup value. Add a helper column that joins two fields into one key, and it can still find, say, the Berlin branch revenue for Q2.
In short: build a key from both criteria in column A, =B2&C2, then look it up with a lookup value built the same way: =VLOOKUP(E2&F2,A2:D9,4,FALSE). The helper column has to be the first column of the table.
Why a plain VLOOKUP is not enough
A sales report lists every branch several times, once per quarter. A normal VLOOKUP for "Berlin" stops at the first Berlin row it meets. Whether that row is Q1 or Q3 depends purely on how the list happens to be ordered. What you need is a column where every branch and quarter combination appears exactly once.
Step 1: build the helper column
Insert a column to the left of the data and join the two criteria with the & operator. Berlin and Q2 become BerlinQ2. That key is unique even though Berlin and Q2 each show up several times. Try it below; the formula is filled down.
Task Each item number appears in several warehouses. To let VLOOKUP search by warehouse and item at the same time, you need a helper column. In A2, join the warehouse and the item number without a separator, for example HamburgA-104. The formula is filled down to A8.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Key | Warehouse | Item no. | Stock |
| 2 | Hamburg | A-104 | 120 | |
| 3 | Berlin | A-101 | 85 | |
| 4 | Hamburg | A-101 | 40 | |
| 5 | Leipzig | A-104 | 64 | |
| 6 | Berlin | A-104 | 15 | |
| 7 | Leipzig | A-102 | 230 | |
| 8 | Hamburg | A-102 | 98 |
The helper column goes on the left because VLOOKUP always searches the first column of its table. If it gets in the way, hide it. Hidden columns keep calculating.
Step 2: look up the joined value
Here is the trick: join the lookup values too. E2&F2 returns BerlinQ2, which is exactly the text VLOOKUP searches for in the helper column.
Task Column A already joins branch and quarter (=B2&C2). In G2, use VLOOKUP to return the revenue of the branch in E2 for the quarter in F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Branch | Quarter | Revenue $ | Branch | Quarter | Revenue $ |
| 2 | HamburgQ2 | Hamburg | Q2 | 48200 | Berlin | Q2 | |
| 3 | BerlinQ1 | Berlin | Q1 | 51900 | |||
| 4 | LeipzigQ2 | Leipzig | Q2 | 33700 | |||
| 5 | HamburgQ1 | Hamburg | Q1 | 45100 | |||
| 6 | BerlinQ2 | Berlin | Q2 | 56300 | |||
| 7 | LeipzigQ1 | Leipzig | Q1 | 29800 | |||
| 8 | HamburgQ3 | Hamburg | Q3 | 50600 | |||
| 9 | BerlinQ3 | Berlin | Q3 | 54400 |
| Argument | What goes in | In the example |
|---|---|---|
| lookup_value | Both criteria, joined exactly like the helper column | E2&F2 |
| table_array | Starts with the helper column | A2:D9 |
| col_index_num | Counted from the helper column | 4 (revenue) |
| range_lookup | Exact match, because the keys are not sorted | FALSE |
Do not drop FALSE. The keys are in no particular order, and without FALSE VLOOKUP falls back to approximate match and quietly returns some unrelated row.
Add a separator to stay safe
Without a separator, two different pairs can produce the same key. Item 12 for customer 34 and item 123 for customer 4 both turn into 1234. A dash or a pipe in between prevents that: =B2&"-"&C2. Just remember to build the lookup value the same way, E2&"-"&F2. The next exercise compares supplier prices, and its helper column uses a dash.
Task Several suppliers offer the same item at different prices. Helper column A joins item number and supplier with a dash (=B2&"-"&C2). In G2, use VLOOKUP to return the price for the item in E2 from the supplier in F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Item no. | Supplier | Price $ | Item no. | Supplier | Price $ |
| 2 | 4712-Weber KG | 4712 | Weber KG | 18.4 | 4711 | Weber KG | |
| 3 | 4711-Becker AG | 4711 | Becker AG | 12.9 | |||
| 4 | 4715-Schmidt GmbH | 4715 | Schmidt GmbH | 7.8 | |||
| 5 | 4711-Schmidt GmbH | 4711 | Schmidt GmbH | 11.5 | |||
| 6 | 4712-Becker AG | 4712 | Becker AG | 17.9 | |||
| 7 | 4715-Weber KG | 4715 | Weber KG | 8.2 | |||
| 8 | 4711-Weber KG | 4711 | Weber KG | 12.4 |
Three or more criteria work the same way: =B2&"-"&C2&"-"&D2 in the helper column and E2&"-"&F2&"-"&G2 as the lookup value.
No helper column: XLOOKUP and INDEX MATCH
XLOOKUP can join whole columns inside the formula, so you skip the helper column: =XLOOKUP(E2&F2,B2:B9&C2:C9,D2:D9). Another pattern tests each condition separately and multiplies the results: =XLOOKUP(1,(B2:B9=E2)*(C2:C9=F2),D2:D9). Both are explained step by step on XLOOKUP with multiple criteria.
The multiply pattern works with INDEX and MATCH as well: =INDEX(D2:D9,MATCH(1,(B2:B9=E2)*(C2:C9=F2),0)). You may also come across an older trick where CHOOSE builds a two-column table in memory for VLOOKUP. It works, but it is hard to read and easy to break, so it is not covered here.
When the result is a number and every combination appears only once, SUMIFS returns the same value: =SUMIFS(D2:D9,B2:B9,E2,C2:C9,F2). That does not work for text results.
Common mistakes
#N/A although the combination exists
The lookup value and the key are built differently: reversed order (Q2Berlin instead of BerlinQ2), a separator on one side only, or a stray space. Put =E2&F2 in a spare cell and compare it with the helper column character by character.
Dates inside the key
Joining a date with & turns it into its serial number, something like 46023. That is fine as long as both sides are built the same way. For a readable key, wrap the date in TEXT, for example TEXT(C2,"mm/yyyy").
Helper column not on the far left
If the key sits to the right of the values you want back, VLOOKUP cannot reach them. Move the helper column to the start of the table or switch to XLOOKUP.
Duplicate combinations
If the same pair appears twice, VLOOKUP silently returns the first one. Flag duplicates with =COUNTIF(A:A,A2)>1.
VLOOKUP with multiple criteria in German Excel
| English | German |
|---|---|
=VLOOKUP(E2&F2,A2:D9,4,FALSE) | =SVERWEIS(E2&F2;A2:D9;4;FALSCH) |
=XLOOKUP(E2&F2,B2:B9&C2:C9,D2:D9) | =XVERWEIS(E2&F2;B2:B9&C2:C9;D2:D9) |
| INDEX, MATCH, CHOOSE, SUMIFS | INDEX, VERGLEICH, WAHL, SUMMEWENNS |
The & operator is the same in both languages, and the exercises accept either spelling.
Which Excel versions support it?
The helper column with VLOOKUP works in every version of Excel, including Excel 2016, 2019, Excel for Mac and Excel for the web. That is the main reason to use it. XLOOKUP needs Microsoft 365, Excel 2021 or Excel 2024. The INDEX MATCH version calculates whole columns at once: in versions with dynamic arrays (Microsoft 365, Excel 2021 and later) you just press Enter, while Excel 2019 and older need it entered as a legacy array formula with Ctrl+Shift+Enter.
Frequently asked questions
Can VLOOKUP use two criteria?
Not directly, it has a single lookup value. The helper column turns two criteria into one: you join the key and the lookup value with &.
How many criteria can I combine?
As many as you need. Keep adding cells with &, in the same order in the helper column and in the lookup value.
Can I do a VLOOKUP with multiple criteria without a helper column?
With VLOOKUP alone only through the CHOOSE trick, which is hard to maintain. XLOOKUP or INDEX with MATCH handle it without any helper column.
Can I hide the helper column?
Yes. Hidden columns keep calculating. Just do not delete it, or the column number no longer fits and the formulas return #REF! or wrong values.