The VLOOKUP function
Looks for a value in the first column of a table and returns the value from another column in the same row.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value
- The value to find in the first column.
- table_array
- The table, starting with the column to search.
- col_index_num
- Which column of the table to return, counting from 1.
- range_lookup (optional)
- FALSE for an exact match. TRUE or left out finds the closest smaller value in a sorted column.
Examples
D1
=VLOOKUP(1"Pear",2A2:B4,32,4FALSE)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 6 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Find 1Pear in the first column of 2A2:B4 and return the value 3in the table's second column, 4only on an exact match.
D1=VLOOKUP("Kiwi",A2:B4,2,FALSE)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | #N/A | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
Practice VLOOKUP
Read the guide
Questions
Why does VLOOKUP return #N/A?
The value is not in the first column of the table. Check for extra spaces, numbers stored as text, or a missing FALSE at the end.
Can VLOOKUP look to the left?
No. It only returns columns to the right of the one it searches. Use XLOOKUP or INDEX and MATCH instead.