How to Use Nested VLOOKUP in Excel (3 Criteria)

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.


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.

Nested VLOOKUP to Extract Product Price

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 IDs in the Result Table.

Result of Nested VLOOKUP to Extract Product Price

Look at the above picture, we got the Price of all the product IDs 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.

Nested VLOOKUP to Get Sales Value

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 Products in the Result Table.

Result of Nested VLOOKUP to Get Sales Value

Look at the above picture, we got the Sales value of all the Products 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 Products 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.

Nested VLOOKUP with IFERROR Function

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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo