VLOOKUP — Look Up a Value from a Table
The most-used lookup function in Excel. Give it a value, a table, and which column to return — VLOOKUP walks down the leftmost column, finds a match, and returns the value from the column you named. Everything you need is on this page: syntax, five worked examples, the six errors it throws, and a free workbook to open in Excel.
Syntax breakdown
VLOOKUP takes three required arguments plus one optional but critical one. Get the fourth wrong and the whole formula misbehaves — this is the argument that separates confident users from frustrated ones.
| Argument | Type | What it does |
|---|---|---|
lookup_value |
REQUIRED | The value you're searching for. Can be a number, text (in quotes), a logical value, or a cell reference. Case-insensitive for text. Wildcards * and ? work in exact-match mode. |
table_array |
REQUIRED | The range of cells containing your data. The lookup value must be in the leftmost column of this range. Use absolute references ($A$2:$E$100) so the range doesn't shift when you copy the formula down. |
col_index_num |
REQUIRED | Which column to return the answer from, counted from the LEFT of your table array. Column 1 is the lookup column itself; column 2 is one to the right, and so on. Must be a positive integer. |
range_lookup |
OPTIONAL | FALSE or 0 = exact match (almost always what you want). TRUE or 1 = approximate match, which requires the first column to be sorted ascending — used for grade brackets, tax brackets, and any numeric range lookup. Default is TRUE — always specify FALSE for text lookups. |
FALSE at the end, VLOOKUP defaults to approximate match — which silently returns wrong values whenever your table isn't sorted. When you're looking up names, IDs, or SKUs, always end with , FALSE).
Five working examples
Every example below is a real formula from the free workbook. Open the download in Excel and change the yellow cells to see the answers update live.
01 Basic exact match: employee lookup
Given an employee's name, return their salary, department, and manager.
| A · Name | B · Dept | C · Salary | D · Manager |
|---|---|---|---|
| Emma Thompson | Operations | $108,160 | Sarah Chen |
| David Kim | Engineering | $141,440 | Marcus Rivera |
| Sofia Rodriguez | Design | $99,840 | Priya Kumar |
| Michael Chen | Marketing | $87,360 | Diana Lee |
| Aisha Patel | Support | $72,800 | Tom Baxter |
| James Wilson | Analytics | $62,400 | Sarah Chen |
Change only the col_index_num (2, 3, 4…) to pull different fields for the same person. Same lookup value, different columns, one formula per field.
02 Approximate match: convert scores to letter grades
Given a numeric score (0–100), return the letter grade using a sorted grade table.
| A · Min Score | B · Grade | C · GPA |
|---|---|---|
| 0 | F | 0.0 |
| 60 | D | 1.0 |
| 70 | C | 2.0 |
| 80 | B | 3.0 |
| 90 | A | 4.0 |
03 Handle missing values with IFERROR
Show "Not Found" instead of the ugly N/A error when the lookup fails.
| Lookup name | Without IFERROR | With IFERROR |
|---|---|---|
| David Kim | $141,440 | $141,440 |
| Nobody Here | #N/A | Not Found |
| Wrong Name | #N/A | Not Found |
You can also return 0, an empty string "", or any fallback value. Use IFNA instead of IFERROR if you want to catch only N/A errors and let other errors like REF or VALUE surface for debugging.
04 Two-way lookup: VLOOKUP + MATCH
Pick a product AND a month — dynamically return the intersection.
| Product | Jan | Feb | Mar | Apr | May | Jun |
|---|---|---|---|---|---|---|
| Widget A | $12,400 | $13,100 | $11,800 | $14,200 | $15,300 | $14,100 |
| Widget B | $8,700 | $9,200 | $8,900 | $10,100 | $10,800 | $11,400 |
| Gizmo Pro | $22,500 | $24,100 | $21,800 | $23,600 | $25,400 | $26,100 |
| Gizmo Lite | $6,300 | $6,800 | $7,100 | $7,400 | $7,900 | $8,200 |
| Bundle X | $18,900 | $19,700 | $20,200 | $21,300 | $22,100 | $21,800 |
MATCH("Apr", A1:G1, 0) finds "Apr" in the header row → position 5. VLOOKUP then uses 5 as its col_index_num. Change either the product or the month and the answer updates instantly — no hard-coded column number.
05 Multi-sheet lookup: sales log × product master
Your sales log has just SKUs — pull the product name, category, and unit price from a separate Product Master sheet.
| SKU | Product Name | Category | Unit Price |
|---|---|---|---|
| SKU-001 | Widget A | Widgets | $24.99 |
| SKU-002 | Widget B | Widgets | $32.50 |
| SKU-003 | Gizmo Pro | Gizmos | $89.00 |
| SKU-004 | Gizmo Lite | Gizmos | $49.99 |
| SKU | Product Name | Category | Qty | Line Total |
|---|---|---|---|---|
| SKU-003 | Gizmo Pro | Gizmos | 8 | $712.00 |
| SKU-001 | Widget A | Widgets | 24 | $599.76 |
| SKU-002 | Widget B | Widgets | 15 | $487.50 |
Note the single quotes around 'Product Master' — required whenever a sheet name contains a space. Use absolute references ($A:$D) so the formula can be copied down thousands of rows without breaking.
Interactive playground
Try it Live VLOOKUP demonstration
This is what the workbook shows you. Change the yellow cells → the blue answer updates instantly.
Download the workbook to experiment with your own values in a real spreadsheet.
Common errors and how to fix them
VLOOKUP throws six distinct errors depending on what went wrong. Each has a specific cause and a specific fix — memorize this table and you'll debug in seconds instead of minutes.
| Error | Why it happens | Broken → Fix |
|---|---|---|
| #N/A | The lookup value doesn't exist in the leftmost column of the table array. | =VLOOKUP("Z999", Products, 2, FALSE)
=IFERROR(VLOOKUP("Z999", Products, 2, FALSE), "Not Found") |
| #N/A | Trailing spaces in the lookup value or in the table — invisible but breaks exact match. | =VLOOKUP("A100 ", Products, 2, FALSE)
=VLOOKUP(TRIM("A100 "), Products, 2, FALSE) |
| #N/A | Numbers stored as text (or vice versa). '100' as text does not match 100 as number. | =VLOOKUP(TEXT(100,"0"), Products, 2, FALSE)
=VLOOKUP(VALUE("100"), Products, 2, FALSE) |
| #REF! | col_index_num is larger than the number of columns in the table array. |
=VLOOKUP("A100", Products, 5, FALSE) /* only 3 cols */
=VLOOKUP("A100", Products, 3, FALSE) |
| #VALUE! | col_index_num is less than 1 (must be a positive integer). |
=VLOOKUP("A100", Products, 0, FALSE)
=VLOOKUP("A100", Products, 2, FALSE) |
| Wrong result | Fourth argument omitted, table not sorted — approximate match returns silently wrong values. | =VLOOKUP("B200", Products, 3)
=VLOOKUP("B200", Products, 3, FALSE) |
, FALSE). Always.
Related functions
VLOOKUP has a family. Here are the five you should know next — each solves a limitation VLOOKUP has:
Excel version compatibility
VLOOKUP has been in Excel since 1985. It works everywhere spreadsheets exist.
| Platform | Supports VLOOKUP? | Notes |
|---|---|---|
| Excel 365 (Windows & Mac) | ✓ Yes | Preferred: XLOOKUP for new work |
| Excel 2021 | ✓ Yes | XLOOKUP also available |
| Excel 2019 | ✓ Yes | No XLOOKUP — VLOOKUP is your friend |
| Excel 2016 & earlier | ✓ Yes | Full support since Excel 1.0 (1985) |
| Excel for the web | ✓ Yes | Identical behavior |
| Excel on iPad & iPhone | ✓ Yes | Full support |
| Google Sheets | ✓ Yes | Same syntax, same behavior |
| LibreOffice Calc | ✓ Yes | Same syntax, same behavior |
| Apple Numbers | ✓ Yes | Same syntax, slightly different UI |
When to use VLOOKUP vs. alternatives
VLOOKUP is the default choice for beginners, but seasoned analysts often reach for INDEX/MATCH or XLOOKUP instead. Here's a decision tree:
Use VLOOKUP when…
- You need broad compatibility. Your workbook will be opened in Excel 2016, 2013, or older — XLOOKUP won't work there.
- Your lookup column is on the left. The classic setup: SKU on the left, price on the right. VLOOKUP was built for this.
- You're teaching or documenting. Everyone recognizes VLOOKUP. It's the most-searched Excel function on the planet.
- The table is small. On 1,000-row tables, VLOOKUP is fast enough that its performance ceiling doesn't matter.
Use XLOOKUP when…
- You're on Excel 365 or Excel 2021+ and don't need to worry about backward compatibility.
- Your lookup column is to the RIGHT of the answer — VLOOKUP can't look left, but XLOOKUP can.
- You want built-in "if not found" handling — no need to wrap in IFERROR.
- You need to look up the LAST match (searching bottom-up) — XLOOKUP has a search-mode argument for this; VLOOKUP always returns the first match.
Use INDEX + MATCH when…
- You need the widest compatibility AND flexibility — INDEX/MATCH works in every Excel version and handles left-lookups.
- Your table has many columns and you need speed — INDEX/MATCH is measurably faster than VLOOKUP on wide tables because it only reads two columns instead of scanning across.
- You're building complex financial models where insertion of a new column shouldn't break your formulas (VLOOKUP's hard-coded
col_index_numis fragile; MATCH resolves it dynamically).
How VLOOKUP actually works
The exact match algorithm
When you specify FALSE (or 0) as the fourth argument, VLOOKUP scans the leftmost column of your table top-to-bottom, cell by cell, comparing each to your lookup value. On the first exact match, it stops, jumps right by col_index_num - 1 columns, and returns that cell's value. If it reaches the end without finding a match, it returns #N/A.
Text comparison is case-insensitive: "david kim" matches "David Kim". But trailing spaces, leading spaces, and non-breaking spaces (Chr(160), common in web-pasted data) all count — so "David Kim " won't match "David Kim". Use TRIM() or CLEAN() to strip these when your data comes from external sources.
The approximate match algorithm
When you specify TRUE (or omit the fourth argument), VLOOKUP uses a binary search: it jumps to the middle of the range, checks whether your lookup value is greater or less, then eliminates half the range. It keeps halving until it finds the largest value ≤ your lookup. Because this depends on ordering, an unsorted table gives silently wrong answers — VLOOKUP won't warn you.
Approximate match is the right choice for range lookups: grades (0–59 = F, 60–69 = D…), tax brackets, shipping tiers, commission bands. For anything else — names, IDs, SKUs, codes — always use FALSE.
Wildcards in exact-match mode
When range_lookup is FALSE, VLOOKUP supports two wildcards in text lookups:
*matches any sequence of characters.=VLOOKUP("David*", A:B, 2, FALSE)matches "David Kim", "David Wallace", "Davidson", etc.?matches exactly one character.=VLOOKUP("SKU-00?", A:B, 2, FALSE)matches "SKU-001" through "SKU-009" but not "SKU-010".
If you need to look up a literal * or ?, prefix with a tilde: "~*" matches a literal asterisk.
Performance notes
On small tables (fewer than 10,000 rows), VLOOKUP is fast enough that performance doesn't matter. On larger tables — especially when you're recalculating many VLOOKUPs at once — three characteristics start to hurt:
What makes VLOOKUP slow
- Whole-column references.
=VLOOKUP(A2, Sheet2!$A:$Z, 5, FALSE)tells Excel to scan a million-row range even if your data ends at row 5,000. Use a defined range instead:=VLOOKUP(A2, Sheet2!$A$2:$Z$5000, 5, FALSE). - Wide tables. Each VLOOKUP reads the entire row up to
col_index_num— even columns you don't need. INDEX/MATCH reads only two columns and is ~30% faster on tables with more than 20 columns. - Recalculation on every keystroke. If your workbook has 50,000 VLOOKUPs and you're in Automatic calculation mode, every cell edit triggers a full recalc. Switch to Manual (Formulas → Calculation Options → Manual) and press F9 when you're ready to recalculate.
When to switch to INDEX/MATCH
The 2013 Microsoft study on formula performance found INDEX/MATCH averaged ~7% faster than VLOOKUP on typical tables and ~30% faster on very wide tables (100+ columns). For most workbooks the difference is imperceptible — but if you're building financial models with hundreds of thousands of lookups, that difference adds up to minutes of waiting per recalc.
The other reason to prefer INDEX/MATCH: it survives column insertions. If someone inserts a new column between column A and column C, your =VLOOKUP(..., ..., 3, FALSE) now returns the wrong field. INDEX/MATCH resolves the target column by name via MATCH, so insertions are harmless.
How to write a VLOOKUP from scratch
-
Identify the lookup value
What are you searching for? An employee name, a SKU, an ID, a score? This becomes your first argument.
-
Find the table with the answer
Locate the range containing your data. The lookup column MUST be leftmost. If it isn't, rearrange the columns or use INDEX/MATCH instead.
-
Count columns from the left
The lookup column is column 1. Count over to the column that holds the answer you want — that's your
col_index_num. -
Choose exact or approximate match
Type
FALSEunless you're doing a range lookup (grades, tax brackets, tiers). Approximate match is a footgun for beginners — start with FALSE. -
Wrap in IFERROR for safety
In any real workbook, wrap the whole thing in IFERROR so missing values show a friendly message instead of #N/A propagating into downstream formulas.
Functions used with VLOOKUP
These functions show up around VLOOKUP constantly in real spreadsheets:
Frequently asked questions
What does VLOOKUP stand for?
Vertical Lookup. It searches VERTICALLY down the leftmost column of a table. Its sibling, HLOOKUP, does the same thing horizontally across the top row.
Why does my VLOOKUP return #N/A when I can see the value in the table?
Nine times out of ten it's one of three things: (1) trailing or leading whitespace — use TRIM() to clean it; (2) numbers stored as text vs. actual numbers — use VALUE(); or (3) the lookup value is in a column OTHER than the leftmost of your table array — VLOOKUP can only search the leftmost column.
Can VLOOKUP look to the left?
No. VLOOKUP always returns a column to the right of the lookup column. If you need to look up in column C and return column A, use INDEX(A:A, MATCH(...)) or XLOOKUP instead.
When should I use TRUE vs FALSE at the end?
Use FALSE (exact match) for almost every real-world lookup — names, IDs, SKUs, product codes. Use TRUE (approximate match) only when you're looking up a numeric value in a sorted range table: grades, tax brackets, shipping tiers, commission bands. When in doubt, use FALSE.
How do I VLOOKUP with multiple criteria?
VLOOKUP itself takes only one lookup value. To match on multiple criteria, either (a) create a helper column that concatenates your criteria and search on that: =VLOOKUP(A2&B2, HelperTable, 3, FALSE), or (b) switch to XLOOKUP with concatenated ranges, or (c) use INDEX with a SUMPRODUCT-style array formula. The helper-column approach is by far the simplest.
Why is my VLOOKUP returning the wrong result?
If the answer looks plausible but is wrong, you almost certainly forgot the fourth argument. Without FALSE, VLOOKUP defaults to approximate match and returns the largest value ≤ your lookup — which happens to look like a valid answer. Always end with , FALSE) for text lookups.
Is VLOOKUP being deprecated?
No. Microsoft has stated VLOOKUP will remain in Excel indefinitely. XLOOKUP is a modern alternative for Excel 365 and 2021+, but VLOOKUP is not going away — millions of legacy workbooks depend on it, and Microsoft's backward-compatibility commitment protects it. Feel free to keep using VLOOKUP.
Can I VLOOKUP across multiple sheets or workbooks?
Yes. Reference the other sheet in the table_array argument: =VLOOKUP(A2, 'Product Master'!$A:$D, 2, FALSE). For another workbook, include the file name in square brackets: =VLOOKUP(A2, '[Products.xlsx]Master'!$A:$D, 2, FALSE). The other workbook must be open for the reference to update automatically; if it's closed, Excel caches the last-known value.
What's the maximum table size VLOOKUP can handle?
The same as any Excel range: 1,048,576 rows and 16,384 columns per sheet. Performance-wise, VLOOKUP stays snappy up to 100,000 rows on modern hardware; beyond that, switch to INDEX/MATCH or Power Query for merges.
How is VLOOKUP different from XLOOKUP?
Three big differences: (1) XLOOKUP can look LEFT — VLOOKUP can only return columns to the right; (2) XLOOKUP defaults to exact match — VLOOKUP defaults to approximate; (3) XLOOKUP has a built-in "if not found" argument — VLOOKUP needs IFERROR to handle misses. But XLOOKUP only works in Excel 365 and 2021+, so VLOOKUP still wins on compatibility.
Can VLOOKUP return multiple values?
Not directly — a single VLOOKUP always returns one value from one column. To pull multiple fields (name, price, category, stock), write one VLOOKUP per field, changing only col_index_num. To get all rows matching a criterion, use FILTER (Excel 365) or a helper-column approach with INDEX/SMALL.
Why is VLOOKUP case-insensitive and can I make it case-sensitive?
VLOOKUP treats text as case-insensitive by design — "DAVID" matches "david". To do a case-sensitive lookup, replace VLOOKUP with an array formula using EXACT: =INDEX(B:B, MATCH(TRUE, EXACT(A:A, "David"), 0)) — press Ctrl+Shift+Enter in older Excel versions.
Templates that use VLOOKUP
See VLOOKUP in action inside these free templates from our library:
Skip the syntax. Ask in plain English.
The Sheets & Cells AI Add-in writes VLOOKUP, XLOOKUP, INDEX/MATCH, and every other formula for you — right inside Excel. Type "look up David Kim's salary from the employee table" and get the working formula, ready to paste.
Try the AI Add-in →VLOOKUP — Look Up a Value from a Table
The most-used lookup function in Excel. Give it a value, a table, and which column to return — VLOOKUP walks down the leftmost column, finds a match, and returns the value from the column you named. Everything you need is on this page: syntax, five worked examples, the six errors it throws, and a free workbook to open in Excel.
Syntax breakdown
VLOOKUP takes three required arguments plus one optional but critical one. Get the fourth wrong and the whole formula misbehaves — this is the argument that separates confident users from frustrated ones.
| Argument | Type | What it does |
|---|---|---|
lookup_value |
REQUIRED | The value you're searching for. Can be a number, text (in quotes), a logical value, or a cell reference. Case-insensitive for text. Wildcards * and ? work in exact-match mode. |
table_array |
REQUIRED | The range of cells containing your data. The lookup value must be in the leftmost column of this range. Use absolute references ($A$2:$E$100) so the range doesn't shift when you copy the formula down. |
col_index_num |
REQUIRED | Which column to return the answer from, counted from the LEFT of your table array. Column 1 is the lookup column itself; column 2 is one to the right, and so on. Must be a positive integer. |
range_lookup |
OPTIONAL | FALSE or 0 = exact match (almost always what you want). TRUE or 1 = approximate match, which requires the first column to be sorted ascending — used for grade brackets, tax brackets, and any numeric range lookup. Default is TRUE — always specify FALSE for text lookups. |
FALSE at the end, VLOOKUP defaults to approximate match — which silently returns wrong values whenever your table isn't sorted. When you're looking up names, IDs, or SKUs, always end with , FALSE).
Five working examples
Every example below is a real formula from the free workbook. Open the download in Excel and change the yellow cells to see the answers update live.
01 Basic exact match: employee lookup
Given an employee's name, return their salary, department, and manager.
| A · Name | B · Dept | C · Salary | D · Manager |
|---|---|---|---|
| Emma Thompson | Operations | $108,160 | Sarah Chen |
| David Kim | Engineering | $141,440 | Marcus Rivera |
| Sofia Rodriguez | Design | $99,840 | Priya Kumar |
| Michael Chen | Marketing | $87,360 | Diana Lee |
| Aisha Patel | Support | $72,800 | Tom Baxter |
| James Wilson | Analytics | $62,400 | Sarah Chen |
Change only the col_index_num (2, 3, 4…) to pull different fields for the same person. Same lookup value, different columns, one formula per field.
02 Approximate match: convert scores to letter grades
Given a numeric score (0–100), return the letter grade using a sorted grade table.
| A · Min Score | B · Grade | C · GPA |
|---|---|---|
| 0 | F | 0.0 |
| 60 | D | 1.0 |
| 70 | C | 2.0 |
| 80 | B | 3.0 |
| 90 | A | 4.0 |
03 Handle missing values with IFERROR
Show "Not Found" instead of the ugly N/A error when the lookup fails.
| Lookup name | Without IFERROR | With IFERROR |
|---|---|---|
| David Kim | $141,440 | $141,440 |
| Nobody Here | #N/A | Not Found |
| Wrong Name | #N/A | Not Found |
You can also return 0, an empty string "", or any fallback value. Use IFNA instead of IFERROR if you want to catch only N/A errors and let other errors like REF or VALUE surface for debugging.
04 Two-way lookup: VLOOKUP + MATCH
Pick a product AND a month — dynamically return the intersection.
| Product | Jan | Feb | Mar | Apr | May | Jun |
|---|---|---|---|---|---|---|
| Widget A | $12,400 | $13,100 | $11,800 | $14,200 | $15,300 | $14,100 |
| Widget B | $8,700 | $9,200 | $8,900 | $10,100 | $10,800 | $11,400 |
| Gizmo Pro | $22,500 | $24,100 | $21,800 | $23,600 | $25,400 | $26,100 |
| Gizmo Lite | $6,300 | $6,800 | $7,100 | $7,400 | $7,900 | $8,200 |
| Bundle X | $18,900 | $19,700 | $20,200 | $21,300 | $22,100 | $21,800 |
MATCH("Apr", A1:G1, 0) finds "Apr" in the header row → position 5. VLOOKUP then uses 5 as its col_index_num. Change either the product or the month and the answer updates instantly — no hard-coded column number.
05 Multi-sheet lookup: sales log × product master
Your sales log has just SKUs — pull the product name, category, and unit price from a separate Product Master sheet.
| SKU | Product Name | Category | Unit Price |
|---|---|---|---|
| SKU-001 | Widget A | Widgets | $24.99 |
| SKU-002 | Widget B | Widgets | $32.50 |
| SKU-003 | Gizmo Pro | Gizmos | $89.00 |
| SKU-004 | Gizmo Lite | Gizmos | $49.99 |
| SKU | Product Name | Category | Qty | Line Total |
|---|---|---|---|---|
| SKU-003 | Gizmo Pro | Gizmos | 8 | $712.00 |
| SKU-001 | Widget A | Widgets | 24 | $599.76 |
| SKU-002 | Widget B | Widgets | 15 | $487.50 |
Note the single quotes around 'Product Master' — required whenever a sheet name contains a space. Use absolute references ($A:$D) so the formula can be copied down thousands of rows without breaking.
Interactive playground
Try it Live VLOOKUP demonstration
This is what the workbook shows you. Change the yellow cells → the blue answer updates instantly.
Download the workbook to experiment with your own values in a real spreadsheet.
Common errors and how to fix them
VLOOKUP throws six distinct errors depending on what went wrong. Each has a specific cause and a specific fix — memorize this table and you'll debug in seconds instead of minutes.
| Error | Why it happens | Broken → Fix |
|---|---|---|
| #N/A | The lookup value doesn't exist in the leftmost column of the table array. | =VLOOKUP("Z999", Products, 2, FALSE)
=IFERROR(VLOOKUP("Z999", Products, 2, FALSE), "Not Found") |
| #N/A | Trailing spaces in the lookup value or in the table — invisible but breaks exact match. | =VLOOKUP("A100 ", Products, 2, FALSE)
=VLOOKUP(TRIM("A100 "), Products, 2, FALSE) |
| #N/A | Numbers stored as text (or vice versa). '100' as text does not match 100 as number. | =VLOOKUP(TEXT(100,"0"), Products, 2, FALSE)
=VLOOKUP(VALUE("100"), Products, 2, FALSE) |
| #REF! | col_index_num is larger than the number of columns in the table array. |
=VLOOKUP("A100", Products, 5, FALSE) /* only 3 cols */
=VLOOKUP("A100", Products, 3, FALSE) |
| #VALUE! | col_index_num is less than 1 (must be a positive integer). |
=VLOOKUP("A100", Products, 0, FALSE)
=VLOOKUP("A100", Products, 2, FALSE) |
| Wrong result | Fourth argument omitted, table not sorted — approximate match returns silently wrong values. | =VLOOKUP("B200", Products, 3)
=VLOOKUP("B200", Products, 3, FALSE) |
, FALSE). Always.
Related functions
VLOOKUP has a family. Here are the five you should know next — each solves a limitation VLOOKUP has:
Excel version compatibility
VLOOKUP has been in Excel since 1985. It works everywhere spreadsheets exist.
| Platform | Supports VLOOKUP? | Notes |
|---|---|---|
| Excel 365 (Windows & Mac) | ✓ Yes | Preferred: XLOOKUP for new work |
| Excel 2021 | ✓ Yes | XLOOKUP also available |
| Excel 2019 | ✓ Yes | No XLOOKUP — VLOOKUP is your friend |
| Excel 2016 & earlier | ✓ Yes | Full support since Excel 1.0 (1985) |
| Excel for the web | ✓ Yes | Identical behavior |
| Excel on iPad & iPhone | ✓ Yes | Full support |
| Google Sheets | ✓ Yes | Same syntax, same behavior |
| LibreOffice Calc | ✓ Yes | Same syntax, same behavior |
| Apple Numbers | ✓ Yes | Same syntax, slightly different UI |
When to use VLOOKUP vs. alternatives
VLOOKUP is the default choice for beginners, but seasoned analysts often reach for INDEX/MATCH or XLOOKUP instead. Here's a decision tree:
Use VLOOKUP when…
- You need broad compatibility. Your workbook will be opened in Excel 2016, 2013, or older — XLOOKUP won't work there.
- Your lookup column is on the left. The classic setup: SKU on the left, price on the right. VLOOKUP was built for this.
- You're teaching or documenting. Everyone recognizes VLOOKUP. It's the most-searched Excel function on the planet.
- The table is small. On 1,000-row tables, VLOOKUP is fast enough that its performance ceiling doesn't matter.
Use XLOOKUP when…
- You're on Excel 365 or Excel 2021+ and don't need to worry about backward compatibility.
- Your lookup column is to the RIGHT of the answer — VLOOKUP can't look left, but XLOOKUP can.
- You want built-in "if not found" handling — no need to wrap in IFERROR.
- You need to look up the LAST match (searching bottom-up) — XLOOKUP has a search-mode argument for this; VLOOKUP always returns the first match.
Use INDEX + MATCH when…
- You need the widest compatibility AND flexibility — INDEX/MATCH works in every Excel version and handles left-lookups.
- Your table has many columns and you need speed — INDEX/MATCH is measurably faster than VLOOKUP on wide tables because it only reads two columns instead of scanning across.
- You're building complex financial models where insertion of a new column shouldn't break your formulas (VLOOKUP's hard-coded
col_index_numis fragile; MATCH resolves it dynamically).
How VLOOKUP actually works
The exact match algorithm
When you specify FALSE (or 0) as the fourth argument, VLOOKUP scans the leftmost column of your table top-to-bottom, cell by cell, comparing each to your lookup value. On the first exact match, it stops, jumps right by col_index_num - 1 columns, and returns that cell's value. If it reaches the end without finding a match, it returns #N/A.
Text comparison is case-insensitive: "david kim" matches "David Kim". But trailing spaces, leading spaces, and non-breaking spaces (Chr(160), common in web-pasted data) all count — so "David Kim " won't match "David Kim". Use TRIM() or CLEAN() to strip these when your data comes from external sources.
The approximate match algorithm
When you specify TRUE (or omit the fourth argument), VLOOKUP uses a binary search: it jumps to the middle of the range, checks whether your lookup value is greater or less, then eliminates half the range. It keeps halving until it finds the largest value ≤ your lookup. Because this depends on ordering, an unsorted table gives silently wrong answers — VLOOKUP won't warn you.
Approximate match is the right choice for range lookups: grades (0–59 = F, 60–69 = D…), tax brackets, shipping tiers, commission bands. For anything else — names, IDs, SKUs, codes — always use FALSE.
Wildcards in exact-match mode
When range_lookup is FALSE, VLOOKUP supports two wildcards in text lookups:
*matches any sequence of characters.=VLOOKUP("David*", A:B, 2, FALSE)matches "David Kim", "David Wallace", "Davidson", etc.?matches exactly one character.=VLOOKUP("SKU-00?", A:B, 2, FALSE)matches "SKU-001" through "SKU-009" but not "SKU-010".
If you need to look up a literal * or ?, prefix with a tilde: "~*" matches a literal asterisk.
Performance notes
On small tables (fewer than 10,000 rows), VLOOKUP is fast enough that performance doesn't matter. On larger tables — especially when you're recalculating many VLOOKUPs at once — three characteristics start to hurt:
What makes VLOOKUP slow
- Whole-column references.
=VLOOKUP(A2, Sheet2!$A:$Z, 5, FALSE)tells Excel to scan a million-row range even if your data ends at row 5,000. Use a defined range instead:=VLOOKUP(A2, Sheet2!$A$2:$Z$5000, 5, FALSE). - Wide tables. Each VLOOKUP reads the entire row up to
col_index_num— even columns you don't need. INDEX/MATCH reads only two columns and is ~30% faster on tables with more than 20 columns. - Recalculation on every keystroke. If your workbook has 50,000 VLOOKUPs and you're in Automatic calculation mode, every cell edit triggers a full recalc. Switch to Manual (Formulas → Calculation Options → Manual) and press F9 when you're ready to recalculate.
When to switch to INDEX/MATCH
The 2013 Microsoft study on formula performance found INDEX/MATCH averaged ~7% faster than VLOOKUP on typical tables and ~30% faster on very wide tables (100+ columns). For most workbooks the difference is imperceptible — but if you're building financial models with hundreds of thousands of lookups, that difference adds up to minutes of waiting per recalc.
The other reason to prefer INDEX/MATCH: it survives column insertions. If someone inserts a new column between column A and column C, your =VLOOKUP(..., ..., 3, FALSE) now returns the wrong field. INDEX/MATCH resolves the target column by name via MATCH, so insertions are harmless.
How to write a VLOOKUP from scratch
-
Identify the lookup value
What are you searching for? An employee name, a SKU, an ID, a score? This becomes your first argument.
-
Find the table with the answer
Locate the range containing your data. The lookup column MUST be leftmost. If it isn't, rearrange the columns or use INDEX/MATCH instead.
-
Count columns from the left
The lookup column is column 1. Count over to the column that holds the answer you want — that's your
col_index_num. -
Choose exact or approximate match
Type
FALSEunless you're doing a range lookup (grades, tax brackets, tiers). Approximate match is a footgun for beginners — start with FALSE. -
Wrap in IFERROR for safety
In any real workbook, wrap the whole thing in IFERROR so missing values show a friendly message instead of #N/A propagating into downstream formulas.
Functions used with VLOOKUP
These functions show up around VLOOKUP constantly in real spreadsheets:
Frequently asked questions
What does VLOOKUP stand for?
Vertical Lookup. It searches VERTICALLY down the leftmost column of a table. Its sibling, HLOOKUP, does the same thing horizontally across the top row.
Why does my VLOOKUP return #N/A when I can see the value in the table?
Nine times out of ten it's one of three things: (1) trailing or leading whitespace — use TRIM() to clean it; (2) numbers stored as text vs. actual numbers — use VALUE(); or (3) the lookup value is in a column OTHER than the leftmost of your table array — VLOOKUP can only search the leftmost column.
Can VLOOKUP look to the left?
No. VLOOKUP always returns a column to the right of the lookup column. If you need to look up in column C and return column A, use INDEX(A:A, MATCH(...)) or XLOOKUP instead.
When should I use TRUE vs FALSE at the end?
Use FALSE (exact match) for almost every real-world lookup — names, IDs, SKUs, product codes. Use TRUE (approximate match) only when you're looking up a numeric value in a sorted range table: grades, tax brackets, shipping tiers, commission bands. When in doubt, use FALSE.
How do I VLOOKUP with multiple criteria?
VLOOKUP itself takes only one lookup value. To match on multiple criteria, either (a) create a helper column that concatenates your criteria and search on that: =VLOOKUP(A2&B2, HelperTable, 3, FALSE), or (b) switch to XLOOKUP with concatenated ranges, or (c) use INDEX with a SUMPRODUCT-style array formula. The helper-column approach is by far the simplest.
Why is my VLOOKUP returning the wrong result?
If the answer looks plausible but is wrong, you almost certainly forgot the fourth argument. Without FALSE, VLOOKUP defaults to approximate match and returns the largest value ≤ your lookup — which happens to look like a valid answer. Always end with , FALSE) for text lookups.
Is VLOOKUP being deprecated?
No. Microsoft has stated VLOOKUP will remain in Excel indefinitely. XLOOKUP is a modern alternative for Excel 365 and 2021+, but VLOOKUP is not going away — millions of legacy workbooks depend on it, and Microsoft's backward-compatibility commitment protects it. Feel free to keep using VLOOKUP.
Can I VLOOKUP across multiple sheets or workbooks?
Yes. Reference the other sheet in the table_array argument: =VLOOKUP(A2, 'Product Master'!$A:$D, 2, FALSE). For another workbook, include the file name in square brackets: =VLOOKUP(A2, '[Products.xlsx]Master'!$A:$D, 2, FALSE). The other workbook must be open for the reference to update automatically; if it's closed, Excel caches the last-known value.
What's the maximum table size VLOOKUP can handle?
The same as any Excel range: 1,048,576 rows and 16,384 columns per sheet. Performance-wise, VLOOKUP stays snappy up to 100,000 rows on modern hardware; beyond that, switch to INDEX/MATCH or Power Query for merges.
How is VLOOKUP different from XLOOKUP?
Three big differences: (1) XLOOKUP can look LEFT — VLOOKUP can only return columns to the right; (2) XLOOKUP defaults to exact match — VLOOKUP defaults to approximate; (3) XLOOKUP has a built-in "if not found" argument — VLOOKUP needs IFERROR to handle misses. But XLOOKUP only works in Excel 365 and 2021+, so VLOOKUP still wins on compatibility.
Can VLOOKUP return multiple values?
Not directly — a single VLOOKUP always returns one value from one column. To pull multiple fields (name, price, category, stock), write one VLOOKUP per field, changing only col_index_num. To get all rows matching a criterion, use FILTER (Excel 365) or a helper-column approach with INDEX/SMALL.
Why is VLOOKUP case-insensitive and can I make it case-sensitive?
VLOOKUP treats text as case-insensitive by design — "DAVID" matches "david". To do a case-sensitive lookup, replace VLOOKUP with an array formula using EXACT: =INDEX(B:B, MATCH(TRUE, EXACT(A:A, "David"), 0)) — press Ctrl+Shift+Enter in older Excel versions.
Templates that use VLOOKUP
See VLOOKUP in action inside these free templates from our library:
Skip the syntax. Ask in plain English.
The Sheets & Cells AI Add-in writes VLOOKUP, XLOOKUP, INDEX/MATCH, and every other formula for you — right inside Excel. Type "look up David Kim's salary from the employee table" and get the working formula, ready to paste.
Try the AI Add-in →