VLOOKUP is one of Excel's most used functions, and also one of the most misunderstood. It looks up a value in the first column of a range and returns a value from a column to the right. Here's how to use it correctly.
The syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value — what you're searching for.
- table_array — the range to search in. The lookup value must be in the first column of this range.
- col_index_num — which column of the range to return, counting from the left starting at 1.
- range_lookup — FALSE for an exact match (what you want almost always), TRUE for an approximate match.
A worked example
Say you have employee IDs in column A and names in column B, and you want to find the name for ID 1043:
=VLOOKUP(1043, A2:B100, 2, FALSE)
This searches column A for 1043, and returns the value from column 2 of the range (column B) — the name. The FALSE forces an exact match.
The mistakes almost everyone makes
Forgetting FALSE. If you leave off the last argument, VLOOKUP defaults to approximate match (TRUE), which requires your data to be sorted and often returns wrong results silently. Use FALSE for exact matches unless you specifically need a range lookup.
The lookup value isn't in the first column. VLOOKUP can only look right. The value you search for must sit in the leftmost column of your table_array. If your ID is in column C and the name is in column A, VLOOKUP can't do it — you'd need INDEX/MATCH instead.
col_index_num counts within the range, not the sheet. If your range is B2:E100, column 1 is B, column 2 is C, and so on — not the spreadsheet's column letters.
Column insertions break it. Because col_index_num is a hardcoded number, inserting a column inside your range shifts everything and returns the wrong column. This is a common source of silent errors.
Handling not-found results
When VLOOKUP can't find the value, it returns #N/A. Wrap it in IFERROR to show something friendlier:
=IFERROR(VLOOKUP(1043, A2:B100, 2, FALSE), "Not found")
When to use something else
VLOOKUP's left-to-right-only limitation and its fragility to column changes are real drawbacks. If you have a modern version of Excel, XLOOKUP solves both problems and is worth learning. If you're on an older version, INDEX/MATCH is the more flexible classic alternative. But for straightforward left-column lookups, VLOOKUP is quick and does the job.