The** VLOOKUP** function is one of Microsoft Excel’s most powerful, flexible, and extremely useful functions to search and retrieve values – either exactly matched values or the closest matched values – by looking up a corresponding value. But sometimes, using only one **VLOOKUP** doesn’t get the job done. In that case, we can apply multiple **VLOOKUP**. In this article, we will show you how to implement **Nested VLOOKUP** in Excel.

**Table of Contents**hide

**Download Workbook**

You can download the free practice Excel workbook from here.

**VLOOKUP in Excel**

**VLOOKUP** stands for ‘**Vertical Lookup**‘. It is a function that makes Excel search for a certain value in a column, in order to return a value from a different column in the same row.

**Generic formula:**

`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

Here,

Arguments | Definition |
---|---|

lookup_value |
The value you are trying to match |

table_array |
The data range that you want to search your value |

col_index_num |
Corresponding column of the lookup_value |

range_lookup |
This is a Boolean value: TRUE or FALSE. FALSE (or 0) means exact match and TRUE (or 1) means approximate match. |

**3 Criteria to Use Nested VLOOKUP in Excel**

In this section, you will know how to get **product price **and** sales value** by applying **Nested VLOOKUP** in Excel. We will also let you know how to combine **The IFERROR Function** with the **Nested VLOOKUP** in Excel.

**1. Implementation of Nested VLOOKUP to Extract Product Price**

Consider the following dataset. In the **Result Table**, we want to get the **Price** based on the product **ID**. But both of them are not together in any single table. **ID** is in **Table 1** and **Price** is in **Table 2**. So what we are going to do here is, we will search for the **ID** in **Table 1** and based on the match value, we will extract the **Price** from Table** 2** and show the result in the **Price** column in the **Result Table**.

**Steps:**

- Click on the cell that you want the
**Price**of the**ID**(e.g. cell beside**ID A101**in the**Result Table**,**I5**) - And write the following formula,

`=VLOOKUP(VLOOKUP(H5, $B$5:$C$9, 2, FALSE), $E$5:$F$9, 2, FALSE)`

Here,

**H5** = **A101**, the **ID** that we stored in the **Result Table** as the lookup value**$B$5:$C$9** = Data range in **Table 1** to search the lookup value**$E$5:$F$9** = Data range in **Table 2** to search the lookup value**2** = Column index number to search the lookup value**FALSE** = As we want an exact match, so we put the argument as **FALSE**.

- Press
**Ctrl + Shift + Enter**on your keyboard.

You will get the **Price** (**$50**) of **ID A101** in the result cell (**I5**).

- Drag the row down by
**Fill Handle**to apply the formula to the rest of the rows to get the**Price**of all the product**ID**s in the**Result Table**.

Look at the above picture, we got the **Price** of all the product **ID**s in the **Result Table** by running a **Nested VLOOKUP** formula.

**Formula Breakdown:**

**VLOOKUP(H5, $B$5:$C$9, 2, 0)****Output: “Football”****Explanation:****H5**=**A101,**search in the data range**$B$5:$C$9**, that is in the column index**2**(**B****Column**), with the help of**FALSE**to get an exact match of the**Product**name “**Football”**.

**VLOOKUP(VLOOKUP(H5, $B$5:$C$9,2,0), $E$5:$F$9, 2, 0)**-> becomes**VLOOKUP(“Football”, $E$5:$F$9, 2, 0)****Output: $50****Explanation:**“**Football”,**search in the data range**$E$5:$F$9**, to get an exact match (**FALSE**) of the**Price**,**$50**.

**2. Application of Nested VLOOKUP to Get Sales Value**

Consider the following dataset. In the **Result Table**, we want to get the **Sales** value based on the **Product** name. So what we are going to do here is, we will search for the **Product** name in **Table 1** and based on the match value, we will extract the **Sales** value from the **Table 2** and show the result in the **Sales** column in the **Result Table**.

**Steps:**

- Click on the cell that you want the
**Sales**value of the**Product**(e.g. cell beside**Product****Football**in the**Result Table**,**J5**) - And write the following formula,

`=VLOOKUP(VLOOKUP(I5,$B$5:$C$9,2,0),$E$5:$G$9,2,0)`

Here,

**I5** = **Football**, the **Product** name that we stored in the **Result Table** as the lookup value**$B$5:$C$9** = Data range in **Table 1** to search the lookup value**$E$5:$G$9** = Data range in **Table 2** to search the lookup value**2** = Column index number to search the lookup value**0** = As we want an exact match, so we put the argument as **0** or **FALSE**.

- Press
**Ctrl + Shift + Enter**on your keyboard.

You will get the **Sales** value (**$1,000**) of **Football** in the result cell (**J5**).

- Drag the row down by
**Fill Handle**to apply the formula to the rest of the rows to get the**Sales**value of all the**Product**s in the**Result Table**.

Look at the above picture, we got the **Sales** value of all the **Product**s in the **Result Table** by running a **Nested VLOOKUP** formula.

**Formula Breakdown:**

**VLOOKUP(I5, $B$5:$C$9, 2, 0)****Output: “David”****Explanation:****I5**=**Football,**search in the data range**$B$5:$C$9**, that is in the column index**2**(**B****Column**), with the help of**0**to get an exact match of the**Salesman**name “**David”**.

**VLOOKUP(VLOOKUP(I5, $B$5:$C$9, 2, 0), $E$5:$G$9, 2, 0)**-> becomes**VLOOKUP(“David”, $E$5:$G$9, 2, 0)****Output: $1,000****Explanation:**“**David”,**search in the data range**$E$5:$G$9**, to get an exact match (**0**) of the**Sales**value**$1,000**.

**3. Combination of Nested VLOOKUP and IFERROR Function**

In this section, we will see how to combine **Nested VLOOKUP** and **IFERROR** functions to extract a certain result from multiple tables based on one single lookup value.

Look at the following dataset where **Product**s are distributed in 3 different tables. We will search for a specific **Product** based on the **ID (A106) **among those 3 tables and display the result in the **Product** column of the** Result Table.**

**Steps:**

- Click on the cell that you want the
**Product**name (e.g. cell beside**ID A106**in the**Result Table**,**L5**) - And write the following formula,

`=IFERROR(VLOOKUP(K5,$B$5:$C$7,2,0),IFERROR(VLOOKUP(K5,$E$5:$F$7,2,0),VLOOKUP(K5,$H$5:$I$7,2,0)))`

Here,

**K5** = **A106**, the **ID** that we stored in the **Result Table** as the lookup value**$B$5:$C$7** = Data range in **Table 1** to search the lookup value**$E$5:$F$7** = Data range in **Table 2** to search the lookup value**$H$5:$I$7** = Data range in **Table 3** to search the lookup value**2** = Column index number to search the lookup value**0** = As we want an exact match, so we put the argument as **0** or **FALSE**.

- Press
**Ctrl + Shift + Enter**on your keyboard.

You will get the **Product **name (**Cricket Bat**) of lookup **ID A106** in the result cell (**L5**).

**Formula Breakdown:**

**VLOOKUP(K5, $H$5:$I$7, 2, 0)****Output: #N/A****Explanation:****K5**=**A106,**search in the data range**$H$5:$I$7**to get an exact match (**2**) of the product**ID****A106**. As there is no match of the**ID A106**in the range**$H$5:$I$7 (Table 3),**so it returns**#N/A**error**.**

**VLOOKUP(K5, $E$5:$F$7, 2, 0)****Output: “Cricket Bat”****Explanation:****K5**=**A106,**search in the data range**$E$5:$F$7**with the help of**2**(**FALSE)**to get an exact match of the product**ID****A106**. We found the match of**ID****A106**in that range (**Table 2**), so it returns the “**Cricket Bat”**.

**IFERROR(VLOOKUP(K5, $E$5:$F$7, 2, 0),VLOOKUP(K5, $H$5:$I$7, 2, 0)**-> becomes**IFERROR(“Cricket Bat”, #N/A)****Output: “Cricket Bat”****Explanation:**The**IFERROR**function checks the formula and if it gets an error as the result, it returns another specified value by the user.

**VLOOKUP(K5, $B$5:$C$7, 2, 0)****Output: #N/A****Explanation:****K5**=**A106,**search in the data range**$B$5:$C$7**to get an exact match (**2**) of the product**ID****A106**. As there is no match of the**ID A106**in the range**$B$5:$C$7 (Table 1),**so it returns**#N/A**error**.**

**IFERROR(VLOOKUP(K5,B5:C7,2,0),IFERROR(VLOOKUP(K5,E5:F7,2,0),VLOOKUP(K5,H5:I7,2,0)))**-> becomes**IFERROR(#N/A, “Cricket Bat”)****Output: “Cricket Bat”****Explanation:**The**IFERROR**function checks the formula and if it gets an error as the result, it returns another specified value by the user.

**Keep in Mind**

- As the range of the data table array to search for the value is fixed, don’t forget to put the
**dollar ($)**sign in front of the cell reference number of the array table. - When working with array values, don’t forget to press
**Ctrl + Shift + Enter**on your keyboard while extracting results. Pressing only**Enter**will work only when you are using**Microsoft 365**.

**Conclusion**

This article explained in detail how to use **Nested VLOOKUP** with 3 different criteria in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

**Related Articles**

**How to Use VLOOKUP with SUM Function in Excel (6 Methods)****Excel VLOOKUP to Find the Closest Match (with 5 Examples)****How to Make VLOOKUP Case Sensitive in Excel (4 Methods)****Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)****How to Use the VLOOKUP Function in Excel VBA (4 Examples)**