📎 This article includes 1 downloadable practice file ↓
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. |
💡 On Microsoft 365 or Excel 2021, prefer XLOOKUP: it looks left, defaults to exact match and has a built-in “not found” argument.
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.
📎 Practice files for this article
- 📗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
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.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong