Excel VLOOKUP with Multiple Criteria in Column and Row

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to use the VLOOKUP function with criteria in the column and row in Excel. The VLOOKUP function looks for a value in the leftmost column of a table or array and returns a value from the same row from the specified column. Today, we will discuss 6 examples. Using these examples, you can easily use the VLOOKUP function with multiple criteria in the column and row. So, without any delay, let’s start the discussion.


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

To explain the examples, we will use a dataset that contains information about the First Name, Last Name, Level, and Department of some employees. Some employees have the same First Name while others have the same Last Name. Depending on the First and Last Name, we will look for the value of the Level and Department. For example, if the First Name is Peter and the Last Name is William, then the Level should be 3 and Department should be Sales.

excel vlookup multiple criteria column and row


1. Insert Ampersand (&) Inside Excel VLOOKUP to Join with Multiple Criteria in Column and Row

To use the VLOOKUP function with multiple criteria in the column and row, we need to join the criteria first. For that purpose, we can use the Ampersand (&) operator. Also, we must need a Helper column. Let’s follow the steps below to see how you can use the Ampersand (&) operator inside the VLOOKUP function.

STEPS:

  • First of all, we need to insert a Helper column on the leftmost side of the table or range like the picture below.

Insert Ampersand (&) Inside Excel VLOOKUP to Join with Multiple Criteria in Column and Row

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

  • Secondly, select Cell B5 and type the formula below:
=C5&D5

Insert Ampersand (&) Inside Excel VLOOKUP to Join with Multiple Criteria in Column and Row

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

  • Thirdly, press Enter and drag the Fill Handle down to copy the formula till Cell B13.

Insert Ampersand (&) Inside Excel VLOOKUP to Join with Multiple Criteria in Column and Row

  • After that, 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.

Insert Ampersand (&) Inside Excel VLOOKUP to Join with Multiple Criteria in Column and Row

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. That is why we have typed 4 in the third argument.
  • As we needed the exact match, we typed FALSE in the fourth argument.
  • Similarly, to get the value of the Department, type the formula below in Cell I7:
=VLOOKUP($I$4&$I$5,$B$5:$F$11,5,FALSE)

Insert Ampersand (&) Inside Excel VLOOKUP to Join with Multiple Criteria in Column and Row

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

  • Finally, if you change the Last Name, then the Level and Department will automatically update.

Read More: VLOOKUP with Multiple Criteria Including Date Range in Excel 


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

If you want to avoid the Helper column, then, this example will certainly help you. We can use the CHOOSE function with the VLOOKUP function to add multiple criteria in columns and rows. The CHOOSE function chooses a value or action to perform from a list of values based on an index number. Here, we can use the CHOOSE function to create a concatenated lookup array. We will discuss the formula in the steps below. So, let’s pay attention to the steps below.

STEPS:

  • In the first place, 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.

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

In this formula, we have used the CHOOSE function in the second argument of the VLOOKUP function. Here, 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. So, 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.

  • After that, 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)
  • In the end, press Enter to see the Department of Peter William.

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

Here, 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.

Read More: Excel VLOOKUP with Multiple Criteria in Horizontal & Vertical Way


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

Another way to use the VLOOKUP function for multiple criteria is to use it with the IF function. Also, you don’t need to add any helper columns. If we have two criteria, we will insert the first criteria in the first argument of the VLOOKUP function and the second one inside the IF function. In this way, we can use the VLOOKUP with the IF function. Let’s follow the steps below to learn about the formulas.

STEPS:

  • In the beginning, 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.

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

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

  • In the following step, select Cell H7 and type the formula below:
=VLOOKUP(H4,IF(C5:C11=H5,B5:E11,""),4,FALSE)
  • Finally, press Enter to see the Department name in Cell H7.

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

Read More:  Vlookup with Multiple Criteria without a Helper Column in Excel 


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

If you need to use a Helper column, then you can use the VLOOKUP with the MATCH function for multiple criteria in columns and rows. The MATCH function returns the relative position of an item in an array that matches a specified value. Here, we will use the MATCH function to get the Column Index Number inside the VLOOKUP function. Let’s follow the steps below to learn more about it.

STEPS:

  • Firstly, we need to add a Helper column in Column B.
  • Secondly, 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.
  • After that, you will see results like the picture below.

  • In the following step, 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.

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

In this formula, 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.

  • Also, to get the Department name, type the formula below in Cell I7:
=VLOOKUP($I$4&$I$5,B5:F11,MATCH(F4,B4:F4,0),FALSE)

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

The difference between this formula and the previous one is the part of the MATCH function. Here, 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.

Read More: VLOOKUP with Multiple Criteria and Multiple Results


5. Use Excel VLOOKUP Function with Multiple Criteria in Single Column

In this example, we will use the VLOOKUP function with multiple criteria in a single column in Excel. To do so, we will use the dataset below. We will extract the Level number of two employees based on their First Names.

Let’s observe the steps below to learn more about the method.

STEPS:

  • First of all, select Cell E13 and type the formula below:
=VLOOKUP(C13:C14,B5:E11,3,FALSE)

Use Excel VLOOKUP Function with Multiple Criteria in Single Column

  • Now, press Ctrl + Shift + Enter to see the Level number of Peter and Sophie.

Use Excel VLOOKUP Function with Multiple Criteria in Single Column

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

Read More: How to Use VLOOKUP with Multiple Criteria in Different Columns


6. Insert Drop-Down Lists as Multiple Criteria in VLOOKUP

In Excel, we can also insert drop-down lists as multiple criteria in the VLOOKUP function. The advantage of drop-down lists is that you won’t have to type the criteria manually each time. Rather, you can select the First Name and Last Name using the drop-down list and the formula will return the desired Level value and Department name. Let’s pay attention to the steps below to see how we can insert drop-down lists as multiple criteria.

STEPS:

  • In the first place, select Cell H4.

  • Secondly, go to the Data tab and click on the Data Validation option. A dialog box will appear.

  • Select List in the Allow field.
  • Then, enable editing in the Source box and select the range B5:B11.
  • Click OK to proceed.

  • As a result, you will see a drop-down list in Cell H4.

Insert Drop-Down Lists as Multiple Criteria in VLOOKUP in Excel

  • Repeat the same steps to get a drop-down list in Cell H5.

  • At this moment, select the First and Last Names using the drop-down lists.

  • In the following step, select Cell H6 and type the formula below to get the Level value:
=VLOOKUP(H4,IF(C5:C11=H5,B5:E11,""),3,FALSE)
  • Press Enter to see the result.

Insert Drop-Down Lists as Multiple Criteria in VLOOKUP in Excel

  • Finally, type the formula below in Cell H7 to find the Department name:
=VLOOKUP(H4,IF(C5:C11=H5,B5:E11,""),4,FALSE)
  • Hit Enter for the result.

Insert Drop-Down Lists as Multiple Criteria in VLOOKUP in Excel

Note: We have used this VLOOKUP with the IF function formula in Example 3. You can find the explanation there.

Read More:How to Apply VLOOKUP with Multiple Criteria Using the CHOOSE Function


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In this article, we have 6 ideal examples of Excel VLOOKUP with Multiple Criteria in Column and Row. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to VLOOKUP with Multiple Criteria | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo