How to Apply Double VLOOKUP in Excel (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will discuss how to apply Double VLOOKUP in Excel. Basically, we use the VLOOKUP function to get faster results in Excel. However, before going to the main discussion, let’s introduce the function in brief.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Introduction to the Excel VLOOKUP Function

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order. So, the following is the syntax for the function.

  • Syntax:

=VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup])

  • Arguments:

lookup_value: [required] The value you want to lookup in the first column of a table.

table_array: [required] The range/table from where the function looks for the lookup value.

column_index_num: [required] This is the column index in the table from where to retrieve a value.

range_lookup:  [optional] TRUE = approximate match i.e. if an exact match is not found, the function will return the closest match. FALSE = exact match.


4 Methods to Apply Double VLOOKUP

1. Apply Simply Two VLOOKUP (Nested VLOOKUP)

Often we use two VLOOKUP functions separately in Excel, But, if we use them simultaneously, the result is faster and saves time. For example, we have a dataset containing tables of patients’ records of a hospital. Now, by applying our first VLOOKUP we will look for the Serial No of any patient to find the ID of that patient. Subsequently, we will apply another VLOOKUP to find the Patient’s Name through the ID we have received from the first VLOOKUP.  So, let’s get through the steps.

Steps:

  • First, write the following formula in Cell C5.
=VLOOKUP(VLOOKUP(B5,F9:H11,3,FALSE),B9:D11,2,FALSE)

Apply Simply Two VLOOKUP (Nested VLOOKUP)

  • Next, you will get the Patient’s Name regarding a particular ID.

Apply Simply Two VLOOKUP (Nested VLOOKUP)

  • The above formula will work for any ID in the table. If you change the ID, the Patient’s Name or any other details will change accordingly.

Breakdown of the Formula

(VLOOKUP(B5,F9:H11,3,FALSE)

Here, this part of the formula looks for the value of B5 in table F9:H11 and returns the ID regarding the Serial No of B5.

VLOOKUP(VLOOKUP(B5,F9:H11,3,FALSE),B9:D11,2,FALSE)

Finally, this formula looks for the ID in table B9:D11 and returns the Patient’s Name corresponding to that ID.

Read More: How to Use Nested VLOOKUP in Excel (3 Criteria)


2. Double VLOOKUP with Excel IF and ISTEXT Functions

We can apply the IF function with the two VLOOKUP functions in several ways. In this method, we will use the ISTEXT function along with VLOOKUP and the IF function. Let’s discuss the associated steps.

Steps:

  • Initially, type the following formula in Cell C5.
=IF(ISTEXT(B5),VLOOKUP(B5,C9:D11,2,FALSE),VLOOKUP(B5,B9:D11,3,FALSE))

Double VLOOKUP with Excel IF and ISTEXT Functions

  • Next, you will get the following result if the value of B5 is text.

Double VLOOKUP with Excel IF and ISTEXT Functions

  • Then, if the value of B5 is not text, the following will be the result.

Breakdown of the Formula

ISTEXT(B5)

Here, the ISTEXT function checks whether the value of B5 is Text or not, and replies TRUE or FALSE accordingly.

VLOOKUP(B5,C9:D11,2,FALSE)

Next, if the value of B5 is text then, the VLOOKUP function looks at the value in range C9:D11 (2nd column) and returns the patient’s Problem accordingly.

VLOOKUP(B5,B9:D11,3,FALSE)

Now, if the value of B5 is not text, the VLOOKUP function looks for the value in the range B9:D13 (3rd column) and returns the patient’s Problem accordingly.

IF(ISTEXT(B5),VLOOKUP(B5,C9:D11,2,FALSE),VLOOKUP(B5,B9:D11,3,FALSE))

Finally, the IF function combines the formula and returns the result depending on the changed value of Cell B5.

Read More: VLOOKUP to Search Text in Excel (4 Easy Ways)


Similar Readings


3. Excel IF and ISNA Functions with Two VLOOKUP

Now, in this method, we will use double VLOOKUPs with the ISNA function. Likewise, in Method 2, we will use the IF function in this method too.

Steps:

  • First, select the below formula in Cell C13.
=IF(ISNA(VLOOKUP(C12,B5:D10,3,FALSE)), "Not found",VLOOKUP(C12,B5:D10,3,FALSE))

Excel IF and ISNA Functions with Two VLOOKUP

  • Next, if the ID is present in the range B5:D10, we will get the following result.

Excel IF and ISNA Functions with Two VLOOKUP

  • On the other hand, if the ID is not present in the specified range ‘Not Found’ will be returned.

Breakdown of the Formula

VLOOKUP(C12,B5:D10,3,FALSE)

Here, the VLOOKUP function looks for the ID in range B5:D10; in column 3. (works similar to described in Method 1 & Method 2).

ISNA(VLOOKUP(C12,B5:D10,3,FALSE))

Here, the ISNA function checks whether a value is #N/A, and returns TRUE or FALSE.

In this table, this formula looks for M345 in the range B5:D10 and returns

{FALSE}

IF(ISNA(VLOOKUP(C12,B5:D10,3,FALSE)), “Not found”, VLOOKUP(C12,B5:D10,3,FALSE))

Finally, the IF function checks whether a condition is met, and returns the patient’s Problem if FALSE, and returns ‘Not Found’ if TRUE.

Read More: How to Use IF ISNA Function with VLOOKUP in Excel (3 Examples)


4. Double VLOOKUP with IFERROR Function

In this method, we will apply the IFERROR function with two VLOOKUP. So, here are the steps involved in this method.

Steps:

  • Firstly, type the below formula in Cell C12.
=IFERROR(VLOOKUP(B12,B5:D9,3,FALSE),IFERROR(VLOOKUP(B12,C5:D9,2,FALSE),"Not Found"))

Double VLOOKUP with IFERROR Function

  • Consequently, the formula returns the following result. Use the Fill Handle (+) to copy the formula to the rest of the cells.

Breakdown of the Formula
(VLOOKUP(B12,B5:D9,3,FALSE)

Here, the VLOOKUP looks for the values of B12 in range B5:D9 (3rd Column).

(VLOOKUP(B12,C5:D9,2,FALSE)

Now, this VLOOKUP looks for the value of B12 in range C5:D9 (in 2nd Column).

IFERROR(VLOOKUP(B12,C5:D9,2,FALSE),”Not Found”))

Then, the IFERROR function returns the value_if_error (here ‘Not Found’) if the expression is an error and the value of the expression itself otherwise.

IFERROR(VLOOKUP(B12,B5:D9,3,FALSE),IFERROR(VLOOKUP(B12,C5:D9,2,FALSE),”Not Found”))

Finally, double VLOOKUP functions wrapped with double IFERROR find the value in the dataset, return ‘Muscle Pain’ for ID: E258, otherwise, the formula returns ‘Not Found’.


Conclusion

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo