How to Apply Double VLOOKUP in Excel?

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.


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

Read More: 10 Best Practices with VLOOKUP in Excel


How to Apply Double VLOOKUP in Excel: 4 Methods

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 at 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: 7 Practical Examples of VLOOKUP Function in Excel


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 the 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: How to Use VLOOKUP with Two Lookup Values in Excel


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, returns the patient’s Problem if FALSE, and returns ‘Not Found’ if TRUE.


4. Double VLOOKUP with the 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 the range B5:D9 (3rd Column).

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

Now, this VLOOKUP looks for the value of B12 in the 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, and return ‘Muscle Pain’ for ID: E258, otherwise, the formula returns ‘Not Found’.


Download the Practice Workbook

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


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


<< Go Back to Advanced VLOOKUPExcel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo