DOUBLE VLOOKUP is a term in Advance Excel where two VLOOKUP functions nested with the IFERROR function to make a nested formula that sequentially works in two different tables or columns of the same workbook or different workbooks and retrieves the value. Thus, Double VLOOKUP is often known as IFERROR VLOOKUP or NESTED VLOOKUP.

Broadly, NESTED VLOOKUP is an advanced level of DOUBLE VLOOKUP where two or more VLOOKUP functions work together with two or more IFERROR functions, making a nested formula that sequentially works in two or more different tables or columns of the same workbook or different workbooks. NESTED VLOOKUP is a kind of IFERROR VLOOKUP in Excel.

IFERROR function is a logic function that checks a cell to determine if that cell contains an error or if a formula will result in an error. If no error exists, the function returns the value of the formula. If an error exists, the function returns an error.

The IFERROR function can’t distinguish the type of error. The error could be #NAME, #N/A, #REF, DIV/0, and so on. What is used as the value_if_error argument will be displayed regardless of the error type.

# I. Simple DOUBLE VLOOKUP / IFERROR VLOOKUP / NESTED VLOOKUP

Double VLOOKUP is a term in Advance Excel where two VLOOKUP functions nested with the IFERROR function will make a unique formula, that is able to match the lookup_value in two different columns. It is also known as IFERROR VLOOKUP or NESTED VLOOKUP.

The logic behind DOUBLE VLOOKUP formula or IFERROR VLOOKUP formula is that when the first VLOOKUP fails to find the lookup_value in the first column range returns an Error, then the IFERROR function replaces the Error with the second VLOOKUP function which finds the same lookup_value in the second column range and retrieves the value.

As a result, with a single formula, we can retrieve the value matches with two different columns.

SYNTAX:

STEPS:

=IFERROR(VLOOKUP($G3,$B$3:$D$12,3,0), (VLOOKUP($G3,$C$3:$D$12,2,0)))

• Step 1:

Select the cell to get the result of **Double VLOOKUP** (i.e., H3).

In this cell, press equality “**=**” sign to start formula and just type a few letters ‘=IF….‘ and select the IFERROR function from the given suggestion list with the help of a down arrow (↓), if required.

Then press ‘Tab’ key, IFERROR syntax appears with the open parenthesis.

The IFERROR function has 2 arguments: value and value_if_error.

*Value*is the expression being tested.*Value_if_error*is the text that will be returned if there is an error in the formula (or expression).

We place the first VLOOKUP in the *value *position and place the second VLOOKUP in the *value_if_error *position. Therefore, the new formula works in such a way, if the first VLOOKUP returns any #N/A error, it will be replaced by the value of the second VLOOKUP.

• Step 2:

Then type a few letters ‘=vlo….‘ and select the VLOOKUP function from the given suggestion list.

Then press the ‘Tab’ key, VLOOKUP syntax appears with the open parenthesis within the IFERROR function.

• Step 3:

The first VLOOKUP retrieves the value from a first table_array, likes

=IFERROR( VLOOKUP( $G3, $B$3:$D$12, 3, 0),** **

$G3 – select the

*lookup value*locates in cell G3 (i.e., CAN-1) and fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row.As a result, while the formula is copied to the right side or horizontally, the column address does not change but the row address changes accordingly.

- $B$3:$D$12 –
*lookup_value*found in the range is called*lookup_array*and fixed the range by pressing a single time F4 key. Thus the range is converted to absolute from the relative cell reference.As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

- 3 – the
*column_index_num*is the count of columns between the lookup value column and the return value column. - 0 – the last argument of the VLOOKUP function is
*range_lookup.*If we are looking for an exact match we put either 0 or FALSE.

• Step 4:

When the first VLOOKUP formula cannot find a match, it returns #N/A error (i.e., lookup_value does not find a match in the first column of table_array).

Then the wrapped IFERROR function to replace the #N/A error either with the ‘values’ or ‘suggested texts’. We should place the texts in double quotation marks (” “).

But here the #N/A error is replaced with the values, those values are getting from the Second VLOOKUP.

We should place the second VLOOKUP in place of value_if_error, the second argument of the IFERROR function. Then, we write the formula as:

=IFERROR(VLOOKUP($G3, $B$3:$D$12, 3, 0), (VLOOKUP($G3, $C$3:$D$12, 2, 0)))

$G3 – as the same as first Lookup, select the

*lookup value*locates in cell G3 (i.e., CAN-1) and fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row.As a result, while the formula is copied to the right side or horizontally, the column address does not change but the row address changes accordingly.

- $C$3:$D$12 –
*lookup_value*found in the range is called*lookup_array*and in this case, lookup_array should be different from the first lookup_array.

Fixed the range by pressing a single time F4 key. Thus the range is converted to absolute from the relative cell reference.

As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

- 2 – the
*column_index_num*is the count of columns between the lookup value column and the return value column. - 0 – the last argument of the VLOOKUP function is
*range_lookup.*If we are looking for an exact match we put either 0 or FALSE.

• Step 5:

Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well.

• Step 6: Extend Formula from a cell to the end of the range without Formatting

Copy (Ctrl+C) the cell with formula **➪** selects the “*Formulas and number formats*” (Alt+E+S+R / Alt+Ctrl+V+R) **➪** Select the range to copy the formula (Shift+Down Arrow) **➪** then press **Enter** or click on **OK**.

• Step 7: Observations

**(01)**. In the first case, the *lookup_value* is CAN-1 and Double VLOOKUP or IFERROR VLOOKUP returns the *result value* CAN-124 as a Number Code (UN). The lookup_value found in the range C3:C12.

**(02)**. In the second case, the *lookup_value* is IN-91 and Double VLOOKUP or IFERROR VLOOKUP returns the *result value* IND-356 as a Number Code (UN). The lookup_value found in the different range B3:B12.

**(03)**. In the third case, the *lookup_value* is USA-840 and Double VLOOKUP or IFERROR VLOOKUP returns #N/A error. That means the lookup_value does not exist either in range C3:C12 or B3:B12.

**(04)**. In the fourth case, using another IFERROR function to replace the #N/A error with a text likes “Not Found“. We can keep it blanks ” ” instead of a text.

# II. Advanced DOUBLE VLOOKUP/ NESTED VLOOKUP / IFERROR VLOOKUP

Nested VLOOKUP is an advanced level of multiple VLOOKUP functions inside the IFERROR functions, where individual VLOOKUP function finds the match from the suggested table array and retrieves the value. If the VLOOKUP function fails to match, it returns #N/A error.

The IFERROR function allows replacing the #N/A error with suggested ‘value’ or ‘text’. To make the formula more dynamic, rather than put any value manually replaces it with the second VLOOKUP. Similarly, the second VLOOKUP formula finds the match from another table array and retrieves the value. If it fails to find the match, it returns #N/A error.

Again and again, apply the IFERROR function to replace the #N/A error by third, fourth, fifth,…VLOOKUP functions. The combination of multiple IFERROR and VLOOKUP functions makes a *formula* that allows to sequential lookup with first table array, second table array, third.., fourth…so on.

The advanced method of using IFERROR VLOOKUP in Excel is called the Advanced Double VLOOKUP or Advanced Nested VLOOKUP or Advanced IFERROR VLOOKUP.

SYNTAX:

STEPS:

=IFERROR(VLOOKUP($G3,$B$3:$D$12,3,0), IFERROR(VLOOKUP($G3,$C$3:$D$12,2,0), VLOOKUP($G3,$D$3:$D$12,1,0)))

• Step 1:

Select the cell to get the result of **Advanced Double VLOOKUP** (i.e., H3).

In this cell, press equality “**=**” sign to start formula and just type a few letters ‘=IF….‘ and select the IFERROR function from the given suggestion list with the help of a down arrow (↓), if required.

Then press ‘Tab’ key, IFERROR syntax appears with the open parenthesis.

The IFERROR function has 2 parts: value and value_if_error. We place the first VLOOKUP in the *value *position and place the second VLOOKUP in the *value_if_error *position. Therefore, the new formula works in such a way, if the first VLOOKUP returns any #N/A error, it will be replaced by the value of the second VLOOKUP.

• Step 2:

Then type a few letters ‘=vlo….‘ and select the VLOOKUP function from the given suggestion list.

Then press the ‘Tab’ key, VLOOKUP syntax appears with the open parenthesis within the IFERROR function.

• Step 3:

The first VLOOKUP retrieves the value from a first table_array. This formula allows retrieving the values against match cases and returns the #N/A errors in non-match cases.

In this case, we wrap the formula with another IFERROR function to replace the #N/A error. The #N/A error should be replaced with the value, this value comes from the Second VLOOKUP.

If the second VLOOKUP formula fails to find the match from the range, it returns the #N/A error. This error is replaced by the value of the third VLOOKUP.

The position of the first VLOOKUP in place of the ‘value‘ argument of the *first IFERROR function* and in place of the ‘value_if_error’ argument puts the second IFERROR function. The position of the second VLOOKUP and third VLOOKUP in place of the ‘value‘ and ‘value_if_error‘ arguments in the *second IFERROR function*, respectively.

Therefore, now formula works in such a way, if the first VLOOKUP returns any #N/A error, the second VLOOKUP replaces the error with the value. If the second VLOOKUP returns any #N/A error, it will be replaced by the value of the third VLOOKUP.

=IFERROR(VLOOKUP($G3,$B$3:$D$12,3,0), IFERROR(VLOOKUP($G3,$C$3:$D$12,2,0), VLOOKUP($G3,$D$3:$D$12,1,0)))

$G3 – the

*lookup value*locates in cell G3 (i.e., CAN-1) and same for the first, the second and the third VLOOKUP function.

Fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row.

As a result, while the formula is copied to the right side or horizontally, the column address does not change but the row address changes accordingly.

- $B$3:$D$12 –
*lookup_array*for the first VLOOKUP*(lookup_array*is the range where*lookup_value*is found).

$C$3:$D$12 – *lookup_array *for the second VLOOKUP.

$D$3:$D$12 – *lookup_array *for the third VLOOKUP.

Fixed every range by pressing a single time F4 key. Thus the range is converted to absolute from the relative cell reference.

As a result, the range does not change when the formula is copied to another cell either horizontally or vertically.

- 3 –
*column_index_num*for the first VLOOKUP (the*column_index_num*is the count of columns between the lookup value column and the return value column.

2 – *column_index_num *for the second VLOOKUP.

1 – *column_index_num *for the third VLOOKUP.

- 0 – the last argument of the VLOOKUP function is
*range_lookup.*We are looking for an exact match, so put either 0 or FALSE. Thus,*range_lookup*is the same for three VLOOKUP functions.

• Step 4:

Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well.

Step 5: Extend Formula from a cell to the end of the range without Formatting:

Copy (Ctrl+C) the cell with formula **➪** selects the “*Formulas and number formats*” (Alt+E+S+R / Alt+Ctrl+V+R) **➪** Select the range to copy the formula (Shift+Down Arrow) **➪** then press **Enter** or click on **OK**.

# III. Dynamic DOUBLE VLOOKUP/ NESTED VLOOKUP / IFERROR VLOOKUP

Using MATCH function in Advanced Double VLOOKUP or Advanced Nested VLOOKUP or Advanced IFERROR VLOOKUP makes them dynamic formula.

The MATCH() function returns the position of an item within an array that matches a specific value.

Using the MATCH() function in place of column_index_num automatically updates the column number that makes the dynamic formula.

The Syntax of the MATCH function is as follows:

**Lookup_value**is the item to match. It can be a number, text string, a logical value, or a reference.**Lookup_array**is the table or an array containing all of the values to search.**Match_type**is a number that specifies how the match will be applied.- A
*match_type*of zero (0) finds the first item in the array that is an exact match with the*lookup_value*. - To find the item closest to but less than the
*lookup_value*, use a match_type of -1. - To find the item closest to but greater than the
*lookup_value,*use a*match_type*of 1.

- A

In the last two cases, the values in the lookup_array must be in ascending order for the MATCH function to work correctly.

The *match_type* is optional and will default to 1 if omitted from the arguments

SYNTAX:

STEPS:

• Step 1:

Select the cell to get the result of **Advanced Double VLOOKUP** (i.e., H3).

In this cell, press equality “**=**” sign to start formula and just type a few letters ‘=IF….‘ and select the IFERROR function from the given suggestion list with the help of a down arrow (↓), if required.

Then press ‘Tab’ key, IFERROR syntax appears with the open parenthesis.

The IFERROR function has 2 parts: value and value_if_error. We place the first VLOOKUP in the *value *position and place the second VLOOKUP in the *value_if_error *position. Therefore, the new formula works in such a way, if the first VLOOKUP returns any #N/A error, it will be replaced by the value of the second VLOOKUP.

• Step 2:

Then type a few letters ‘=vlo….‘ and select the VLOOKUP function from the given suggestion list.

Then press the ‘Tab’ key, VLOOKUP syntax appears with the open parenthesis within the IFERROR function.

• Step 3:

The position of the first VLOOKUP in place of the ‘value‘ argument of the *first IFERROR function* and in place of the ‘value_if_error’ argument puts the second IFERROR function. The position of the second VLOOKUP and third VLOOKUP in place of the ‘value‘ and ‘value_if_error‘ arguments in the *second IFERROR function*, respectively.

Therefore, now formula works in such a way, if the first VLOOKUP returns any #N/A error, the second VLOOKUP replaces the error with the value. If the second VLOOKUP returns any #N/A error, it will be replaced by the value of the third VLOOKUP.

=IFERROR(VLOOKUP($G3,$B$3:$D$12, MATCH(H$2,$B$2:$D$2,0),0),

IFERROR(VLOOKUP($G3,$C$3:$D$12, MATCH(H$2,$C$2:$D$2,0),0),

VLOOKUP($G3,$D$3:$D$12, MATCH(H$2,$D$2:$D$2,0),0)))

(01) Explanation of First VLOOKUP function:

VLOOKUP($G3,$B$3:$D$12, MATCH(H$2,$B$2:$D$2,0),0)

• $G3 – the *lookup value* locates in cell G3 (i.e., CAN-1) and same for the first, the second and the third VLOOKUP function.

Fix the Column address by pressing three times the F4 key. Thus the range converts from the relative to the mixed cell reference where it indicates the absolute column and relative row.

• $B$3:$D$12 – *lookup_array *for the first VLOOKUP* (lookup_array* is the range where *lookup_value* is found).

• MATCH(H$2,$B$2:$D$2,0) – we place MATCH() function in place of *column_index_num *to update the column number automatically. Please note that the *lookup column* in both the *lookup_array* of VLOOKUP function and the *lookup_array* of MATCH function should be the same. Otherwise, the formula returns the #N/A error. In both cases, Column B is the *lookup column*.

- H$2 =
*lookup_value*reference to ‘Number Code (UN)’ and fixed the row address by pressing two times F4 Key. As a result, the lookup_value is converted from the relative cell reference to the mixed column cell reference, where it indicates the relative column and absolute row address.

As a result, while the formula is copied to the other cells, the row address does not change but the column address changes accordingly.

- $B$2:$D$2 =
*lookup_value*found in the range is called*lookup_array*and fixed the range by pressing a single time F4 key. Thus the range is converted from the relative cell reference to the absolute cell reference. - 0 = for exact match

• 0 – *range_lookup, * the last argument of VLOOKUP function. The value zero (0) or FALSE signifies the exact match.

(02) Explanation of Second VLOOKUP function:

VLOOKUP($G3, $C$3:$D$12, MATCH(H$2,$C$2:$D$2,0), 0)

We should follow the same steps described in the first VLOOKUP function.

(03) Explanation of Third VLOOKUP function:

VLOOKUP($G3,$D$3:$D$12, MATCH(H$2,$D$2:$D$2,0),0)

We should follow the same steps described in the first VLOOKUP function.

• Step 4:

Finally, press Enter to accept the formula. Formula ends by default and closes the last parenthesis as well.

• Step 5: Extend the Formula from a cell to the end of the range without Formatting:

Copy (Ctrl+C) the cell with formula **➪** selects the “*Formulas and number formats*” (Alt+E+S+R / Alt+Ctrl+V+R) **➪** Select the range to copy the formula (Shift+Down Arrow) **➪** then press **Enter** or click on **OK**.

# Conclusion

**(01).** Double VLOOKUP or IFERROR VLOOKUP or Nested VLOOKUP is the most important formulas in Advance Excel. The formula helps to reduce the time span in data analysis.

**(02).** Multiple VLOOKUPS nested with IFERROR function make an advanced formula in Advance Excel that works sequentially in two or more tables or columns in the same workbook or the different workbooks.

**(03).** Using MATCH function in Advanced Double VLOOKUP or Advanced Nested VLOOKUP or Advanced IFERROR VLOOKUP makes them dynamic formula.

- 03 Types of Excel Cell Reference: Relative, Absolute & Mixed
- Best Excel Lookup Function: How to use VLOOKUP Function in Excel
- 03 Best Ways: Excel REVERSE VLOOKUP | VLOOKUP to the left |
- 05 Best Ways: VLOOKUP Multiple Criteria in Excel

- How to Protect Excel Formula, Cells, Worksheet & Workbook - June 8, 2020
- 08 Best Examples of Excel Conditional Formatting - June 2, 2020
- 04 Best Ways: How to Transpose Data in Excel - May 23, 2020

##### Advance Excel Forum

03 Best Ways: DOUBLE VLOOKUP/ IFERROR VLOOKUP/ NESTED VLOOKUP

**Editor's Rating:**

5