VLOOKUP in Excel: a simple guide with examples
Updated 17 November 2026
VLOOKUP finds a value in one column and brings back the matching value from another column in the same row. Think of it as asking Excel: "find this product, and tell me its price".
The formula
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: what you are looking for, for example a product code in cell
E2. - table_array: the table to search. The value you look for must be in its first (leftmost) column.
- col_index_num: which column of that table to bring back: 1 is the first column, 2 the second, and so on.
- range_lookup: type
FALSEfor an exact match. Use FALSE almost every time.
Worked example
Your product list is in A2:C100: codes in column A, names in B, prices in C. To get the price of the code typed in E2:
=VLOOKUP(E2, $A$2:$C$100, 3, FALSE)
Click the cell
Click where you want the answer, for example
F2.Type the formula
Type
=VLOOKUP(, clickE2, type a comma, then select the tableA2:C100.Lock the table
Press F4 once so the range becomes
$A$2:$C$100. Now you can copy the formula down without the table moving.Finish
Type
, 3, FALSE)and press Enter. Drag the fill handle down to copy it to more rows.
The 4 most common VLOOKUP problems
- #N/A: the value isn't found. Check for extra spaces or numbers stored as text. To show a friendly message instead:
=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). - Wrong answers: you left out
FALSE, so Excel made an approximate match. Add FALSE. - #REF!: the column number is bigger than the number of columns in your table.
- It breaks when copied: the table range wasn't locked with
$signs. Press F4 on the range.
VLOOKUP's limits, and XLOOKUP
VLOOKUP can only look to the right of the first column, and inserting a column can shift the column number. In Microsoft 365, Excel 2021 and newer, and Excel for the web, XLOOKUP fixes both:
=XLOOKUP(E2, A2:A100, C2:C100, "Not found")
You point at the column to search and the column to return, it does an exact match by default, and it can look left. If everyone who uses your file has a recent Excel, use XLOOKUP; for older versions, VLOOKUP still works everywhere.
Questions
Why does VLOOKUP return #N/A?
The value you're looking for isn't in the first column of your table, often because of an extra space or a number stored as text. Wrap the formula in IFERROR to show a friendly message.
Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match. TRUE (approximate match) is only for sorted ranges such as tax bands or grade boundaries.
Is XLOOKUP better than VLOOKUP?
Usually, yes: it can look left, matches exactly by default and doesn't break when you insert columns. It needs Microsoft 365, Excel 2021 or newer, or Excel for the web.
More: How to make a pivot table in Excel (beginner guide) · 20 Excel keyboard shortcuts every beginner should know