Summary
The Excel VLOOKUP function is a powerful tool used to search for a value in the first column of a table and return a value in the same row from a specified column. Perfect for vertical lookups, this function supports both exact and approximate matching, and can also handle wildcards (*, ?) for partial matches. VLOOKUP is commonly used in data analysis, financial modeling, and other areas where quick and accurate data retrieval is crucial.
Syntax
=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])
- lookup_value: The value to search for in the first column of the table.
- table_array: The range of cells containing the table.
- column_index_num: The column number in the table from which to retrieve the value.
- range_lookup: [Optional] TRUE for an approximate match, or FALSE for an exact match.
Return value
A value from the table based on the lookup and provided column index.
How to use
To use VLOOKUP, define the value you want to look up and the table array where the data is located. Specify the column index number from where to pull the data and decide whether you want an exact or approximate match. Remember, VLOOKUP can only search to the right; the lookup value must be in the first column of the table array.
Examples
VLOOKUP with Exact Match
Employee Lookup: To retrieve the department of an employee by their ID:
=VLOOKUP("E123", A2:D100, 4, FALSE)
This searches for the employee ID “E123” and returns the department name from the fourth column.
VLOOKUP with Approximate Match
Grading Scale: To determine the letter grade for a numerical score:
=VLOOKUP(85, GradesTable, 2, TRUE)
Assuming ‘GradesTable’ is a range with numerical scores in the first column and letter grades in the second, this formula returns the approximate letter grade for a score of 85.
VLOOKUP with Wildcards
Partial Text Match: To find a customer’s phone number based on a partial name:
=VLOOKUP("Smith*", A2:C100, 3, FALSE)
This looks for a name that starts with “Smith” in the first column and retrieves the corresponding phone number from the third column.
VLOOKUP for Two-Way Lookup
Dynamic Column Retrieval: To find specific data based on a dynamic column reference:
=VLOOKUP("Product123", A1:Z100, MATCH("Price", A1:Z1, 0), FALSE)
This formula searches for “Product123” and uses MATCH to find the column labeled “Price”, returning the price of the product.
VLOOKUP with IFNA
Error Handling: To return a custom message if no match is found:
=IFNA(VLOOKUP("ID456", A2:B10, 2, FALSE), "No matching ID found")
This searches for “ID456” and returns “No matching ID found” if the ID doesn’t exist in the data.
VLOOKUP with Multiple Criteria
First, create a helper column in your table that concatenates the criteria. For example, if you’re looking up a value based on both a person’s last name and their birth year:
In the table, Column B (Last Name) and Column C (Birth Year), create a helper column in Column A:
=B2&C2
Then use VLOOKUP with the concatenated lookup value:
=VLOOKUP("Smith1980", A2:D100, 4, FALSE)
This formula concatenates “Smith” and “1980” as the lookup value and searches for this concatenated string in the helper column, returning the corresponding value from the fourth column.
Additional Notes
- The lookup column must be the first column in the table_array.
- For an approximate match, the first column in the table_array must be sorted in ascending order.
- VLOOKUP can only look to the right; consider using XLOOKUP or INDEX/MATCH for more flexibility.
- Always be mindful of the range_lookup argument to control whether you’re performing an exact or approximate match.