How to vlookup on a vlookup in Excel?
The VLOOKUP function in Excel is a powerful tool for searching for a value in a table based on a matching value in the first column. When you need to perform a lookup based on another lookup from a different table, you can nest VLOOKUP functions. Here’s how to achieve this with a step-by-step guide.
Prepare the Vlookup formula
First, use VLOOKUP to find the value you want to use as the lookup value for the second VLOOKUP. For instance, if you need to find an ID based on a name stored in another table:
=VLOOKUP(A2, B2:C10, 2, FALSE)
- A2: The cell containing the name to look up.
- B2:C10: The range of the table with names in the first column and IDs in the second column.
- 2: The column number in the range from which to retrieve the value (ID in this case).
- FALSE: Specifies that you want an exact match.
Prepare the second Vlookup
Next, use VLOOKUP to find the final value (e.g., an email address) based on the ID returned by the first VLOOKUP. Assume IDs and email addresses are in another table:
=VLOOKUP(ID, D2:E20, 2, FALSE)
- ID: The value returned by the first VLOOKUP (the ID).
- D2:E20: The range of the table with IDs in the first column and email addresses in the second column.
- 2: The column number in the range from which to retrieve the email address.
- FALSE: Specifies an exact match.
Nest the Vlookup formulas
To nest one VLOOKUP inside another, place the first VLOOKUP function as the lookup_value parameter in the second VLOOKUP. The nested formula will look like this:
=VLOOKUP(VLOOKUP(A2, B2:C10, 2, FALSE), D2:E20, 2, FALSE)
- The first VLOOKUP (VLOOKUP(A2, B2:C10, 2, FALSE)) retrieves the ID based on the name in cell A2.
- The second VLOOKUP (VLOOKUP(ID, D2:E20, 2, FALSE)) uses this ID to find the corresponding email address.
Make sure the IDs in both tables match exactly, including formatting and data types.
