How to Use VLOOKUP in Excel
VLOOKUP finds a value in the first column of a table and returns something from the same row. Here is how to use it.
VLOOKUP syntax
- lookup_value: the value to find.
- table_array: the table to search. The lookup value must be in its first column.
- col_index_num: which column of the table to return, counting from 1.
- range_lookup: FALSE for an exact match, TRUE for an approximate match.
Simple example
Your list has product IDs in column A, names in B and prices in C. To get the price for the ID typed in F2:
The 3 means the third column of A2:C100, which is the price.
Lock the table range
Use absolute references so the range does not move when you copy the formula down: $A$2:$C$100. Press F4 after selecting the range to add the dollar signs.
Exact vs approximate match
Use FALSE for IDs, names and anything that must match exactly. TRUE is for sorted number bands such as tax brackets or grade scales.
Fix #N/A errors
The value is not in the first column, there are extra spaces, or numbers are stored as text in one list and as numbers in the other. Clean the data with TRIM or VALUE, or wrap the formula:
Limits of VLOOKUP
It cannot look to the left and it breaks if you insert a column in the table. Try XLOOKUP in Excel 365 or INDEX MATCH in any version.
Frequently asked questions
What does VLOOKUP stand for?
Vertical lookup. It searches down the first column of a table.
Why is VLOOKUP returning #N/A?
The value is missing, has extra spaces, or the data types differ. Use TRIM and check that both are numbers or both are text.
What is the difference between TRUE and FALSE in VLOOKUP?
FALSE returns an exact match only. TRUE returns the closest smaller value and needs sorted data.
Can VLOOKUP look left?
No. The lookup column must be the first column of the table. Use XLOOKUP or INDEX MATCH instead.
How do I VLOOKUP from another sheet?
Include the sheet name in the range, for example Sheet2!$A$2:$C$100.
Need Excel work done for you?
More free tools: All free calculators · Excel automation services · Excel templates · Budget template