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

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.

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

• 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.

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

• 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

• VLOOKUP Not Working (8 Reasons & Solutions)
• Excel LOOKUP vs VLOOKUP: With 3 Examples
• What Is a Table Array in VLOOKUP? (Explained with Examples)
• Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
• How to VLOOKUP with Multiple Conditions in Excel (2 Methods)

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.

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

• 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.

• 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

• Combine SUMIF and VLOOKUP in Excel (3 Quick Approaches)
• Excel VLOOKUP to Return Multiple Values Vertically
• VLOOKUP to Return Multiple Columns in Excel (4 Examples)
• VLOOKUP Example Between Two Sheets in Excel
• Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)
• How to Use the VLOOKUP Ascending Order in Excel (3 Ways)
• IF and VLOOKUP Nested Function (7 Ways)