VLOOKUP with 2 criteria

VLOOKUP with 2 criteria (Sales Agent and Region) – Excel

Formula for column I7 (Revenue)

=VLOOKUP(H7;C7:D13;2;FALSE)

Formula for column C7 (Helper)

=A7&”|”&B7

Formula for column H7 (Helper)

=F7&”|”&G7

Filename path (be quiet!) : C:\SystemF_SSD_Ext_3_2_2021\Documents\vlookup2_softexperia.xlsx

Download excel file

VLOOKUP with 2 Criteria in Excel
A standard VLOOKUP function only takes one criterion.
In other words, it can search by one column at a time.
It can return the Sales Agent Revenue or the Region Revenue but not the combo.
The first way to fix this problem is using a helper column.
Let me explain.
We will add a new column called “helper”.
In this column C, we’ll create a unique identifier by concatenating the Sales Agent name with the Region name.
To do this, just open a new formula, select the Sales Agent name, add an ampersand, then a pipe in quotes, another ampersand, and finally the Region name.
The formula for cell C7 is =A7&”|”&B7
This gives us a unique value that we can use as a search term in a slightly tweaked regular VLOOKUP.
Bet you didn’t know, but the VLOOKUP search value can be made up of concatenated text values. So, we’ll do the same concatenation we did in the helper column C as the search value. Go to cell H7, pick the Sales Agent, add an ampersand, then a pipe in quotes, another ampersand, and then the Region.
Now for the search range, we only need the helper column C and the Revenue now, right?
Everything else is repeated data.
Go to cell F7, pick a Sales Agent and then to cell G7 and pick a Region.
The helper in cell H7 is a concatenation of cells F7 and G7.
Next, the Revenue is in column number 2 of our range and we are looking for an exact match. And there you have it, the correct number pops up in cell I7 for any combination of Sales Agent (cell F7) and Region (cell G7).
The formula for cell I7 is =VLOOKUP(H7,C7:D13,2,FALSE)