The Excel VLOOKUP function, explained interactively.
VLOOKUP searches for a value in the leftmost column of a table, then returns a value from a column you specify on the same row. The original spreadsheet lookup — change the parameters below and watch the formula resolve in real time.
How to use VLOOKUP
Pick a product ID, a return column, and a match mode — VLOOKUP finds the row and returns the cell at the intersection.
"P003".A2:D6.FALSE = exact match (use 95% of the time). TRUE = approximate, requires a sorted first column.| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Product | Price | Stock |
| 2 | P001 | Wireless Mouse | 29.99 | 145 |
| 3 | P002 | USB-C Cable | 12.50 | 320 |
| 4 | P003 | Laptop Stand | 45.00 | 78 |
| 5 | P004 | Desk Lamp | 38.75 | 56 |
| 6 | P005 | Keyboard | 89.99 | 210 |
| 7 | ||||
| 8 | VLOOKUP result | 45.00 | ||
VLOOKUP syntax and arguments
Four arguments, three always required. The optional fourth is the one that trips most people.
table_array. Literal ("P003"), number, cell reference, or expression. Matching is case-insensitive. Wildcards * and ? work only with FALSE.A2:D6. VLOOKUP searches the first column of this range — not the first column of the sheet. Lock with $ when copying the formula down: $A$2:$D$6.table_array, not the worksheet. Exceed the table width and you get #REF!.FALSE (or 0) asks for an exact match — missing values return #N/A. TRUE (or 1, or omitted) asks for an approximate match: the largest value less than or equal to lookup_value, and requires the first column to be sorted ascending. Unsorted + TRUE produces silently wrong results — worse than an error. Always pass FALSE explicitly unless you are doing bracket/tier lookup.VLOOKUP examples
Three patterns you’ll reach for again and again.
Example 1: VLOOKUP for price lookup from a product catalog
The canonical VLOOKUP: given a product ID, return its price.
Looks for P003 in column A, returns the 3rd column (Price) of that row: 45.00.
Example 2: VLOOKUP with approximate match (grade lookup)
The rare legitimate use of TRUE: the first column is a sorted set of thresholds, and you want the row whose threshold is just below your value.
Given thresholds 0/60/70/80/90 mapping to F/D/C/B/A, searching for 85 finds the largest threshold ≤ 85 (which is 80) and returns its grade: B.
Example 3: VLOOKUP across two sheets (joining by key)
Add a column to one sheet by pulling matching data from another — SQL’s LEFT JOIN rendered in spreadsheet form.
For each employee ID in the current sheet, pulls column 4 (e.g. department) from the Employees sheet. The $ signs lock the range when dragging the formula down.
Example 4: VLOOKUP with wildcards (partial match)
Wildcards let you match partial strings, but only in exact-match mode (FALSE). Use * for any number of characters and ? for a single character.
Finds the first row in column B whose Product starts with “Laptop” and returns that row’s 2nd column from the range (Price: 45.00). Note the range starts at column B because VLOOKUP searches the first column of whatever range you pass.
Example 5: VLOOKUP with IFERROR for graceful fallback
Raw VLOOKUP returns #N/A when the value isn’t found — ugly in a dashboard. Wrap with IFERROR (or the stricter IFNA) to show custom text instead.
IFERROR catches every spreadsheet error — #N/A, #REF!, #VALUE!, and formula typos alike. IFNA is stricter and only catches #N/A, letting other errors bubble up so you notice them. Prefer IFNA when you want silent fallbacks only for “not found”, not for malformed formulas.
Common VLOOKUP errors and fixes
Four failure modes, each with what to check and how to recover.
VLOOKUP returns #N/A
Cause: the value isn’t in the first column of the table.
Check for hidden whitespace (TRIM), mismatched types (text vs number), or a wrong range. For user-facing sheets, wrap in =IFERROR(VLOOKUP(…), "Not found") or =IFNA(…) — see Example 5 above.
VLOOKUP returns #REF!
Cause: col_index_num is larger than the number of columns in table_array.
Widen the range or reduce the index. Remember: the index is relative to the range, not the sheet.
VLOOKUP returns #VALUE!
Cause: col_index_num is less than 1, or the lookup is otherwise malformed.
Ensure the column index is a positive integer. Quotes around numeric values can also trigger this.
VLOOKUP returns the wrong value (no error)
Cause: you used TRUE (or omitted the 4th argument) on an unsorted column.
Pass FALSE explicitly, or sort the first column ascending before doing a bracket lookup. Silent errors are the most expensive kind.
VLOOKUP vs HLOOKUP, INDEX+MATCH & XLOOKUP
Four lookup patterns, from 1985 to today. Pick the one that matches your data’s layout and your Excel version.
| Function | Direction | Looks left? | Default match | Availability |
|---|---|---|---|---|
| VLOOKUP | Vertical | No | Approximate (legacy) | Everywhere |
| HLOOKUP | Horizontal | — | Approximate | Everywhere |
| INDEX + MATCH | Either | Yes | Exact (explicit) | Everywhere |
| XLOOKUP | Either | Yes | Exact (modern) | Microsoft 365 & Sheets |
On a modern version of Excel or Google Sheets, XLOOKUP is the better daily driver: it defaults to exact match, can look leftward, and has a built-in “not found” argument. VLOOKUP remains the most compatible across legacy files and older Excel installs — and it still shows up in interviews.
VLOOKUP frequently asked questions
6.01Why does my VLOOKUP return #N/A?▸
The lookup_value wasn’t found in the first column of table_array. Most common causes: invisible whitespace (=TRIM(A2) on both sides), numbers stored as text, or you forgot FALSE as the 4th argument and the data isn’t sorted. See 5 causes and a fix for each for the full diagnostic.
6.02Can VLOOKUP look to the left?▸
No. VLOOKUP always searches the first column of the range and returns a column to its right. For leftward lookups, use INDEX/MATCH or XLOOKUP.
6.03What’s the difference between FALSE and TRUE in VLOOKUP?▸
FALSE asks for an exact match — missing values return #N/A. TRUE asks for an approximate match: the largest value less than or equal to lookup_value, assuming the first column is sorted ascending. Use FALSE 95% of the time; TRUE only for bracket/tier lookups (grades, tax brackets, shipping tiers).
6.04Should I still learn VLOOKUP if XLOOKUP exists?▸
Yes. VLOOKUP is in nearly every spreadsheet file older than 2020 and interviewers still ask about it. XLOOKUP is the better day-to-day tool on modern versions — but the mental model transfers, and you’ll still read VLOOKUPs other people wrote.
6.05Can VLOOKUP return multiple columns at once?▸
Not directly — each VLOOKUP returns one value. In modern Excel and Google Sheets you can wrap it in {…} array form or use XLOOKUP, which returns an entire row or column in a single call.
6.06Is VLOOKUP case-sensitive?▸
No. "apple" and "APPLE" match the same row. For case-sensitive lookup, combine INDEX/MATCH with EXACT: =INDEX(B:B, MATCH(TRUE, EXACT(A:A, "Apple"), 0)). Press Ctrl+Shift+Enter in legacy Excel; modern Excel and Google Sheets handle this as a regular formula.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.