The Excel VLOOKUP function looks for a value in the first column of a table and returns a value from another column in the same row. It is the most famous function in Excel — and the most error-prone. This guide covers how to use it correctly, the traps that bite almost everyone, and when to use XLOOKUP instead.
E2 in column A and return the matching value from column C (the 3rd column of the table):
FALSE unless you specifically need approximate match — without it, VLOOKUP can silently return the wrong row.
Syntax
| Argument | Description | |
|---|---|---|
lookup_value | Required | The value to find in the first column of the table. |
table_array | Required | The table to search. The lookup column must be the leftmost column of this range. |
col_index_num | Required | The column number within the table to return (1 = the lookup column itself, 2 = next column right, and so on). |
range_lookup | Optional | FALSE = exact match. TRUE = approximate match (the default!) — requires the first column sorted ascending. |
The #1 VLOOKUP trap: if you omit the last argument, VLOOKUP defaults to approximate match. On unsorted data this returns wrong answers with no error at all. Make typing FALSE a reflex.
Exact-match lookup (the everyday use)
The table below lists products with categories and prices. To find the price of the Webcam Pro, search for it in column A and return column 3 of the table:
| A | B | C | E | F | ||
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Lookup | Result | |
| 2 | Laptop Stand | Accessories | $49 | Webcam Pro | $129 | |
| 3 | USB-C Hub | Accessories | $65 | |||
| 4 | Monitor 27in | Displays | $289 | |||
| 5 | Webcam Pro | Video | $129 | |||
| 6 | Desk Mat XL | Accessories | $35 | |||
| 7 | Monitor 32in | Displays | $399 | |||
| 8 | Ring Light | Video | $59 |
The formula in F2 is:
col_index_num is 3 because Price is the third column of the table, counting from the lookup column. Change it to 2 and the formula returns the category instead.
To show a friendly message instead of #N/A when a product is missing, wrap the formula in IFERROR:
Try it: interactive VLOOKUP demo
Build a VLOOKUP against the product table above — and see for yourself what happens when you forget FALSE.
Approximate match done right
Approximate match is genuinely useful for grading scales, tax brackets, and commission tiers — cases where you want "the bracket this number falls into." The table must be sorted ascending by the first column:
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Score at least | Grade | Score | Grade | |
| 2 | 0 | F | 84 | B | |
| 3 | 60 | D | |||
| 4 | 70 | C | |||
| 5 | 80 | B | |||
| 6 | 90 | A |
VLOOKUP finds the largest first-column value that is less than or equal to the lookup value, and returns from that row.
Wildcard lookups
With exact match (FALSE), the lookup value may contain wildcards: * for any characters and ? for exactly one:
Should you still use VLOOKUP?
If everyone opening your file has Excel 2021 or Microsoft 365, XLOOKUP is better in almost every way. VLOOKUP still matters because billions of older spreadsheets use it, and older Excel versions don't have XLOOKUP.
| VLOOKUP limitation | The problem | Modern fix |
|---|---|---|
| Can't look left | The lookup column must be the table's first column | XLOOKUP, or INDEX+MATCH |
| Column counting | col_index_num breaks when columns are inserted or deleted | XLOOKUP points at columns directly |
| Approximate by default | Forgetting FALSE gives silent wrong answers | XLOOKUP defaults to exact match |
| First match only | Can't return all matching rows | FILTER returns every match |
Migrating is mechanical — this VLOOKUP:
becomes this XLOOKUP:
Errors & common pitfalls
#N/A — value not found. Either the value truly isn't in the first column, or it differs invisibly: stray spaces, numbers stored as text on one side, or characters pasted from the web. Clean with TRIM or convert text-numbers, or wrap in IFERROR for legitimate misses.
#REF! — col_index_num too large. Asking for column 4 of a 3-column table. Count only the columns inside table_array.
#VALUE! — col_index_num less than 1. Usually a broken cell reference feeding the column number.
Pitfall: inserted columns shift your answers. col_index_num is a hard-coded count. Insert a column inside the table and every VLOOKUP now returns the wrong column — with no error shown.
Pitfall: lock the table before copying. Copying down without absolute references shifts the table: use $A$2:$C$8, or better, an Excel Table (Ctrl+T) with structured references.
Pitfall: VLOOKUP returns only the first match. Duplicate keys? You'll only ever see the topmost row. Use FILTER to get all of them.
Practice workbook
Frequently asked questions
How do I make VLOOKUP an exact match?
=VLOOKUP(value, table, col, FALSE). Without it, VLOOKUP uses approximate match and can return wrong rows on unsorted data.Can VLOOKUP look to the left?
Is VLOOKUP case-sensitive?
Why does VLOOKUP return #N/A when the value is clearly there?
Can VLOOKUP return multiple matches?
What's the difference between VLOOKUP and XLOOKUP?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class