The VLOOKUP function in Excel is used to search for a value in the first column of a range and then return a value in the same row from a specified column. It stands for “Vertical Lookup,” and is commonly used to retrieve data from a table or range by row.
Here’s a step-by-step guide on how to use it:
Syntax of VLOOKUP:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value you want to search for. This can be a cell reference or a static value.
- table_array: The range of cells that contains the data. Ensure the lookup value is in the first column of this range.
- col_index_num: The column number in the table from which the matching value should be returned. The first column of the table array is column 1.
- range_lookup: [Optional] A logical value (TRUE or FALSE). If TRUE or omitted, VLOOKUP will perform an approximate match; if FALSE, an exact match is required.
Example of VLOOKUP:
Suppose you have the following table in Excel:
| A | B | C |
|——–|————|——–|
| ID | Name | Salary |
| 1 | John | 50000 |
| 2 | Alice | 60000 |
| 3 | Bob | 70000 |
You want to find the salary of the person with ID 2.
=VLOOKUP(2, A2:C4, 3, FALSE)
- Ensure Your Data is Organized:
- Make sure that your data is sorted by the first column, especially if you are using approximate match (TRUE).
- Use the VLOOKUP Function:
- In a cell where you want the result (e.g., D1), enter the formula:
- Explanation:
- `2` is the `lookup_value` (the ID you are searching for).
- `A2:C4` is the `table_array` (the range containing your data).
- `3` is the `col_index_num` (the column from which you want to return the value, in this case, the Salary column).
- `FALSE` indicates that an exact match is required.
- Result:
- In this example, the function will return `60000`, which is the salary associated with ID 2.
Tips:
- Ordering: The first column of `table_array` should be sorted in ascending order for approximate matches.
- Exact vs Approximate Match:
- Use `FALSE` for exact match if the exact value is critical.
- Use `TRUE` or omit the `range_lookup` for an approximate match, which is faster and suitable for large datasets where close matching is acceptable.
- Alternate Functionality:
- For more flexible lookups, consider using `INDEX` and `MATCH` together.
By understanding these basics, you can effectively use VLOOKUP to automate and streamline data retrieval in your Excel spreadsheets.
Unlock Your Potential
Excel
Access
Power BI
