What it does
VLOOKUP looks for a value in the first column of a table and returns a value from a column to the right.
Syntax or pattern
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])5 practical examples
Find a price by product code
Use a product code in A2 to return a price from a product table.
=VLOOKUP(A2, $E$2:$G$100, 3, FALSE)FALSE forces an exact match, which is usually safest for IDs and codes.
Return a department by employee ID
Find the employee department from a staff lookup table.
=VLOOKUP(A2, StaffTable, 4, FALSE)Use stable IDs instead of names when possible.
Use IFERROR with VLOOKUP
Show a friendly message if the lookup fails.
=IFERROR(VLOOKUP(A2, $E$2:$G$100, 3, FALSE), "Not found")This keeps reports cleaner than #N/A errors.
Approximate match for tax rates
Find the correct rate from a sorted threshold table.
=VLOOKUP(B2, TaxTable, 2, TRUE)Approximate match requires the first column to be sorted ascending.
VLOOKUP from another sheet
Return a category from a lookup table on a separate sheet.
=VLOOKUP(A2, Categories!$A:$C, 3, FALSE)External sheet references are common in reporting workbooks.
Common mistakes to avoid
- VLOOKUP cannot look left.
- Column numbers can break if the table structure changes.
- Approximate match can produce unexpected results if the lookup table is not sorted.
Related Excel examples
FAQ
Why does VLOOKUP return #N/A?
The lookup value was not found, or the lookup table has spacing/type differences.
Should I use XLOOKUP instead?
For newer Excel versions, XLOOKUP is usually more flexible.