How to Use the VLOOKUP Function in Excel
Today, we’ll explore the VLOOKUP function available in both Excel and Google Sheets. VLOOKUP is a robust function that searches for a given value in the first column of a table and returns a value from the same row in a specified column. It’s widely utilized for data retrieval from extensive datasets.
Basic Syntax
The basic syntax of the VLOOKUP function is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])lookup_value: The value you want to find in the first column of the table.table_array: The cell range containing the data, where the first column includes the values to search for.col_index_num: The column number from which to retrieve the value, starting with 1 for the first column, 2 for the second, and so on.range_lookup: This is optional. Use TRUE (or leave it blank) to find the nearest match. Use FALSE for an exact match only.
Example Use Cases
Example 1: Basic Lookup
Consider a table listing products and their corresponding prices. You can use VLOOKUP to quickly determine the price of a specific item.
| Product | Price |
|---|---|
| Apple | 1.00 |
| Orange | 0.75 |
| Banana | 0.50 |
To find the price of an Orange, the formula would be:
=VLOOKUP("Orange", A2:B4, 2, FALSE)This will correctly return 0.75 as the price of an Orange.
Example 2: Approximate Match
For a table listing sales data, VLOOKUP can determine which sales bracket a particular value falls within when using an approximate match.
| Sales | Commission |
|---|---|
| 100 | 5% |
| 500 | 7% |
| 1000 | 10% |
If the sales value is in cell D2, use the following formula:
=VLOOKUP(D2, A2:B4, 2, TRUE)This formula will return the appropriate commission rate based on the entered sales amount.
The VLOOKUP function is a potent and adaptable tool in Excel and Google Sheets, ideal for data handling and retrieval in large datasets. It’s a time-saver for any data-driven tasks. Happy lookup!
More information: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1