Excel functions / Lookup & reference
VLOOKUP
Finds a value in the first column of a table and returns a value from another column in the same row.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Arguments
lookup_value | What you are searching for, such as a product name or ID. |
|---|---|
table_array | The table to search. VLOOKUP always looks in its first column. |
col_index_num | Which column of the table to return, counting the first column as 1. |
range_lookup | FALSE for an exact match. Leave it out and Excel uses an approximate match, which is rarely what you want. |
How it works
VLOOKUP is the classic way to pull information from a list: look up an employee ID and return their salary, or look up a product and return its price.
The V stands for vertical, because it searches down a column. It only looks to the right of the column it searches, so the lookup column must be the first column of the range you give it.
Always finish with FALSE unless you specifically want an approximate match. Without it, VLOOKUP can quietly return the wrong row.
Example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Stock | ||
| 2 | A10 | Lamp | $25.00 | 12 | 140 | |
| 3 | B20 | Desk | $140.00 | 3 | ||
| 4 | C30 | Chair | $85.00 | 7 | ||
| 5 | D40 | Shelf | $60.00 | 0 | ||
| 6 |
Result: 140. Searches column B for Desk and returns the 2nd column of B2:D5, the price.
Tips
- If you see #N/A, the value was not found. Check for extra spaces with TRIM, or wrap the formula in IFERROR.
- Inserting a column inside the table can break the column number. XLOOKUP and INDEX/MATCH do not have this problem.
Practise VLOOKUP
Try it yourself in a real spreadsheet, with instant feedback on every answer.