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.

**Table of Contents**Expand

## 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**.

### 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.

**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`

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**.

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

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)`

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**.

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**.

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.

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**.

**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.

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)`

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)`

- Now, 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, 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**.

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

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

**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**