📎 This article includes 2 downloadable practice files ↓
In this article
VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | What it means |
|---|---|
lookup_value |
What to find, e.g. a product code. |
table_array |
The table; the code must be in its first column. |
col_index_num |
Which column of the table to return (1 = first). |
range_lookup |
FALSE for exact match (almost always), TRUE for bands. |
Examples
Example 1
=VLOOKUP(A2, Prices!$A:$C, 3, FALSE)Price of the code in A2 from the third column.
Example 2
=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)Commission rate from a sorted slab table.
Common errors and fixes
| You see | Why, and the fix |
|---|---|
#N/A |
Not found — check spaces (TRIM), numbers stored as text, or wrap in IFNA. |
Wrong column after inserting |
The column number is fixed. INDEX/MATCH or XLOOKUP do not break. |
#REF! |
col_index_num is larger than the table width. |
VLOOKUP in practice
Where you will use it
- Price of an item code
- Employee name from ID
- GST rate by HSN
Worked example
| Code | Formula | Result |
|---|---|---|
| P02 | =IFERROR(VLOOKUP(A2,{"P01","Tea",250;"P02","Coffee",400},2,FALSE),"Not found") | Coffee |
| P01 | =IFERROR(VLOOKUP(A3,{"P01","Tea",250;"P02","Coffee",400},2,FALSE),"Not found") | Tea |
| P09 | =IFERROR(VLOOKUP(A4,{"P01","Tea",250;"P02","Coffee",400},2,FALSE),"Not found") | Not found |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Missing FALSE | Without it VLOOKUP returns wrong matches |
| Inserting columns | Column numbers shift; XLOOKUP avoids this |
Related functions
XLOOKUP · INDEX · MATCH · HLOOKUP
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
⬇ Download all 2 files (ZIP · 39 KB)
- 📗Lookup scenarios workbookVLOOKUP, HLOOKUP, INDEX/MATCH and XLOOKUP side by side — vertical, horizontal, two-way — plus 6 real failure cases and their fixes, with PASS/FAIL checks.⬇ XLSX · 10 KB
- 📗Practice workbook: Lookup & reference17 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.