VLOOKUP in Excel: what it does and how to use it
VLOOKUP searches down the first column of a range for a value, and returns something from a column further to the right on the same row. It is the oldest way of matching two tables together, and still the one most people are taught first.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
When to use it
Reach for it when you have a code, a name or an ID in one sheet and the detail you want sitting in another. A price list, a staff directory, a product catalogue.
What trips people up
Two things catch everyone out. VLOOKUP can only look to the right of the column it matches on. There is a second trap too. If you leave off the last argument, VLOOKUP looks for an approximate match, and on an unsorted list it returns wrong answers without warning you. Always end with FALSE. On Microsoft 365, XLOOKUP has neither problem.
Watch it built, step by step
Questions
What does FALSE at the end mean?
It asks for an exact match. Without it, or with TRUE, VLOOKUP does an approximate match that assumes the first column is sorted and returns the nearest smaller value.
Can VLOOKUP look to the left?
No. It can only return a column to the right of the one it searches. INDEX with MATCH, or XLOOKUP, can return a column on either side.
Why did my VLOOKUP break when I inserted a column?
The column number is fixed, so a new column inside the range shifts every result one column over. XLOOKUP and INDEX with MATCH point at the column itself and are not affected.
Related functions
- XLOOKUP — XLOOKUP takes a value, looks for it in one column, and returns the matching item from another.
- INDEX and MATCH — MATCH finds which position a value sits at in a list, and INDEX returns whatever is at a given position.
- IFERROR — IFERROR runs a formula and, if it comes back as an error, shows what you asked for instead.
