VLOOKUP is a function in Microsoft Excel used to look up and retrieve data from a table based on a search term. It stands for “Vertical Lookup” because it searches vertically down a specific column of a table for a match to the search term and returns a value from the corresponding row.
Basic VLOOKUP syntax:
The basic syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Where:
lookup_value: The value you want to lookup.table_array: The range of cells containing the data you want to lookup in.col_index_num: The column number (starting from 1) of the value you want to return.range_lookup: Optional. Whether you want an exact match (FALSE) or an approximate match (TRUE).
Practice
Suppose you have the following table:
| Employee Name | Salary |
|---|---|
| John | 50000 |
| Jane | 60000 |
| Bob | 55000 |
| Alice | 65000 |
| Mark | 70000 |
You want to look up the salary of the employee named “John”. To do this, you can use the following formula:
=VLOOKUP("John", A2:B6, 2, FALSE)
The result will be 50000, which is the salary of John.
- Using VLOOKUP with approximate match:
Suppose you have the following table:
| Product Name | Price |
|---|---|
| Apples | 2.00 |
| Bananas | 1.50 |
| Oranges | 2.50 |
| Grapes | 3.00 |
| Pineapple | 4.00 |
You want to look up the price of the product named “Aples” (with a typo). To do this, you can use the following formula:
=VLOOKUP("Aples", A2:B6, 2, TRUE)
The result will be 2.00, which is the price of Apples.
- Using VLOOKUP with wildcard characters:
Suppose you have the following table:
| Product Code | Description |
|---|---|
| A-001 | Apples |
| B-002 | Bananas |
| A-003 | Oranges |
| C-004 | Grapes |
| A-005 | Pineapple |
You want to look up the description of the product whose code starts with “A-“. To do this, you can use the following formula:
=VLOOKUP("A-*", A2:B6, 2, FALSE)
The result will be “Apples”, which is the description of the product with the code “A-001”.
- Using VLOOKUP with multiple criteria:
Suppose you have the following table:
| Employee Name | Department | Salary |
|---|---|---|
| John | Sales | 50000 |
| Jane | Marketing | 60000 |
| Bob | Sales | 55000 |
| Alice | HR | 65000 |
| Mark | Sales | 70000 |
You want to look up the salary of the employee named “John” who works in the “Sales” department. To do this, you can use the following formula:
=VLOOKUP("John"&"Sales", A2:C6, 3, FALSE)
The result will be 50000, which is John’s salary.
- Using VLOOKUP with an IF statement:
Suppose you have the following table:
| Product Name | Price | Discount |
|---|---|---|
| Apples | 2.00 | 0.1 |
| Bananas | 1.50 | 0.05 |
| Oranges | 2.50 | 0.2 |
| Grapes | 3.00 | 0.15 |
| Pineapple | 4.00 | 0.25 |
You want to look up the discounted price of the product named “Apples” if the discount rate is greater than 10%. To do this, you can use the following formula:
=IF(VLOOKUP("Apples", A2:C6, 3, FALSE)>0.1, VLOOKUP("Apples", A2:C6, 2, FALSE)*(1-VLOOKUP("Apples", A2:C6, 3, FALSE)), VLOOKUP("Apples", A2:C6, 2, FALSE))
The formula first checks if the discount rate of “Apples” is greater than 10%. If it is, the formula calculates the discounted price by subtracting the discount percentage from 1 and multiplying it by the original price. If the discount rate is less than or equal to 10%, the formula returns the original price.
In this case, the discount rate for “Apples” is 0.1, which is greater than 10%. The formula calculates the discounted price as follows:
=2*(1-0.1) = 1.80
