How to Use Excel VLOOKUP with Multiple Criteria in Column and Row: 6 Methods

Methods 1 – Insert Ampersand Inside Excel VLOOKUP to Join with Multiple Criteria in Column and Row

STEPS:

Insert a Helper column on the leftmost side of the table or range like the picture below.

Note: The range B4:E11 was the main dataset. After adding a Helper column, the range B4:F11 becomes the new dataset.

Select Cell B5 and type the formula below:

=C5&D5

We have used the Ampersand (&) operator to concatenate the texts of Cell C5 and Cell D5.

Press Enter and drag the Fill Handle down to copy the formula till Cell B13.

Select Cell I6 and type the formula below:

=VLOOKUP($I$4&$I$5,$B$5:$F$11,4,FALSE)

Hit Enter to see the result.

This VLOOKUP formula looks for the value PeterWilliam in the range B5:F11 and extracts the Level value.

How Does the Formula Work?

In this formula, the first argument ($I$4&$I$5) denotes PeterWilliam which is the lookup value. We have used absolute cell reference to lock the cells.
The second argument ($B$5:$F$11) is the lookup array where the formula will search for the lookup value.
We want to extract the desired Level value that is situated in the fourth column of the range B4:F11. We have typed 4 in the third argument.
As we needed the exact match, we typed FALSE in the fourth argument.

Get the value of the Department, type the formula below in Cell I7:

=VLOOKUP($I$4&$I$5,$B$5:$F$11,5,FALSE)

We have changed the Column Index Number to 5 as Department is the fifth column of the range B4:F11.

If you change the Last Name, the Level and Department will automatically update.

Method 2 – Excel VLOOKUP with CHOOSE Function to Add Multiple Criteria in Column and Row

STEPS:

Select Cell H6 and type the formula below:

=VLOOKUP($H$4&$H$5,CHOOSE({1,2},$B$5:$B$11&$C$5:$C$11,$D$5:$D$11),2,FALSE)

Press Enter to see the Level of Peter William.

We have used the CHOOSE function in the second argument of the VLOOKUP function. The CHOOSE function forms a table with Columns B, C, and D. In that table, Columns B and C are merged and Column D is the second column. The VLOOKUP formula looks for the value of Cell H4 and Cell H5 inside the newly formed table and extracts the row from the second column of that table. That is how we get the Level value of Peter William.

Type the formula below in Cell H7 to get the Department name:

=VLOOKUP($H$4&$H$5,CHOOSE({1,2},$B$5:$B$11&$C$5:$C$11,$E$5:$E$11),2,FALSE)

Press Enter to see the Department of Peter William.

We have used $E$5:$E$11 in place of $D$5:$D$11. So, the CHOOSE function forms a table with Columns B, C, and E this time.

Method 3 – Join Multiple Criteria in Column and Row by Merging VLOOKUP with IF Function

STEPS:

Select Cell H6 and type the formula below:

=VLOOKUP(H4,IF(C5:C11=H5,B5:E11,""),3,FALSE)

Press Enter to see the Level value.

In this formula, the VLOOKUP function looks for the value of Cell H4 if Column C is equal to the value of Cell H5. Extract the row from Column D of the range B5:E11. You can also use the absolute cell reference to lock the cells.

Select Cell H7 and type the formula below:

=VLOOKUP(H4,IF(C5:C11=H5,B5:E11,""),4,FALSE)

Press Enter to see the Department name in Cell H7.

Method 4 – Apply Excel VLOOKUP with MATCH Function for Multiple Criteria in Column and Row

STEPS:

Add a Helper column in Column B.
Select Cell B5 and type the formula below:

=C5&D5

Hit Enter and drag the Fill Handle down to copy the formula to Cell B11.
You will see results like the picture below.

Select Cell I6 and type the formula below:

=VLOOKUP($I$4&$I$5,B5:F11,MATCH(E4,B4:F4,0),FALSE)

Press Enter to get the Level value.

The MATCH function returns the column index number of Cell E4, which is 4. So, the VLOOKUP formula becomes:

=VLOOKUP($I$4&$I$5,B5:F11,4,FALSE)

which is the same as the formula of Example 1.

Get the Department name, type the formula below in Cell I7:

=VLOOKUP($I$4&$I$5,B5:F11,MATCH(F4,B4:F4,0),FALSE)

The difference between this formula and the previous one is the part of the MATCH function. We have used the MATCH function to look for the column index number of Cell F4 in the range B4:F4. This returns 5. This formula is also similar to the last formula of Example 1.

Method 5 – Use Excel VLOOKUP Function with Multiple Criteria in Single Column

STEPS:

Select Cell E13 and type the formula below:

=VLOOKUP(C13:C14,B5:E11,3,FALSE)

Press Ctrl + Shift + Enter to see the Level number of Peter and Sophie.

Note: This formula has a drawback. The VLOOKUP function always extracts the first matched data, which is why we are getting the Level value of Peter Smith, not of Peter William.