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)
- 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: 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))
- 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 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))
- 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, 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"))
- 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
- How to Use VLOOKUP to Find Duplicates in Two Columns
- How to Use VLOOKUP Function with Exact Match in Excel
- How to Find Second Match with VLOOKUP in Excel
- VLOOKUP and Return All Matches in Excel
- VLOOKUP Fuzzy Match in Excel
- Excel VLOOKUP to Find Last Value in Column
- How to Use VLOOKUP to Search Text in Excel
- How to Apply VLOOKUP by Date in Excel
- Return the Highest Value Using VLOOKUP Function in Excel
- VLOOKUP with Numbers in Excel
<< Go Back to Advanced VLOOKUP |Â Excel VLOOKUP Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
