VLOOKUP: where the table and match setting matter
VLOOKUP still turns up in plenty of shared workbooks. Most problems come down to which column contains the key, which result column is numbered and whether the match is exact.
FALSE requests an exact match, so there is no need to sort these IDs. The number 3 means the third column of the lookup table, not worksheet column 3 in every possible formula. If you move the table to F:H, the return index is still 3.
| Formula | Expected result / interpretation |
|---|---|
| =VLOOKUP(E2,$A$2:$C$4,3,FALSE) | 10 |
| =VLOOKUP(“P-999”,$A$2:$C$4,3,FALSE) | #N/A |
| =VLOOKUP(E2,$A$2:$C$4,2,FALSE) | Pen — column index is relative to A:C |
| Row | A: ID | B: Item | C: Price |
|---|---|---|---|
| 2 | P-101 | Notebook | 40 |
| 3 | P-102 | Pen | 10 |
| 4 | P-103 | Folder | 25 |
Paste this table into A1:C4 and enter P-102 in E2. The key column must be the first column of the selected table.
Retrieve a price using an exact product key
Formula syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
What goes into it
lookup_value
Value to find in the first column.
table_array
Table whose first column contains the lookup key.
col_index_num
Column number within the table to return.
range_lookup
FALSE for exact match; TRUE/omitted for approximate matching.
A quick example first
=VLOOKUP(E2,A:C,3,FALSE)
Finds E2 in A and returns the same-row value from the third column of A:C.
=VLOOKUP(A2,Rates!A:B,2,TRUE)
Uses approximate matching for a sorted threshold table.
Before you copy this into a real sheet
Leaving the final argument out requests approximate matching, which is unsuitable for this ID lookup. Approximate matching has legitimate uses in sorted threshold tables, but a near product ID is not a valid substitute. VLOOKUP returns the first duplicate and cannot return a column to the left of its lookup key. Use INDEX/MATCH or XLOOKUP for that layout. If new columns are inserted, review fixed return indices even when the formula still calculates.
Useful habits
- Use FALSE when an exact match is required.
- Remember that the lookup key must be in the first column of table_array.
- XLOOKUP or INDEX/MATCH can be more flexible for new workbooks.
Where people get caught out
- Leaving range_lookup omitted and accidentally using approximate match.
- Inserting columns and forgetting that col_index_num is a fixed position number.
Other functions worth a look
A detail worth remembering
For product codes, names and IDs, use exact matching unless the data truly calls for a sorted approximate lookup. A plausible wrong match is harder to catch than #N/A.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.