The VLOOKUP function in Excel
VLOOKUP (vertical lookup) searches for a value in the first column of a table and returns the value from the same row in a column you choose. Use it to pull a price by product ID, a name by employee number, or a grade from a score table.
It's one of the most used Excel functions, so you'll meet it in almost every existing workbook. In Excel 365 and Excel 2021, XLOOKUP does the same job with fewer limits, but knowing VLOOKUP is still essential.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | What it means |
|---|---|
| lookup_value | What to look for, e.g. a product ID. Usually a cell reference. |
| table_array | The table to search. The lookup value must be in its first column. |
| col_index_num | Which column of the table to return, counted from the table's first column (1 = first column). |
| range_lookupoptional | FALSE for an exact match. TRUE (the default) for an approximate match on a sorted first column. |
Examples
1. Find a price by product ID
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Stock | Product ID | P-103 | |
| 2 | P-101 | Desk lamp | 24.5 | 40 | Price | 39.9 | |
| 3 | P-102 | Office chair | 149 | 12 | |||
| 4 | P-103 | Monitor stand | 39.9 | 25 | |||
| 5 | P-104 | Keyboard | 59 | 0 |
Excel finds P-103 in the first column of A2:D5, moves to the 3rd column of that table (Price) and returns 39.9. FALSE makes it an exact match.
2. Return a different column
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Stock | Stock of P-104 | |
| 2 | P-101 | Desk lamp | 24.5 | 40 | 0 | |
| 3 | P-102 | Office chair | 149 | 12 | ||
| 4 | P-103 | Monitor stand | 39.9 | 25 | ||
| 5 | P-104 | Keyboard | 59 | 0 |
Same lookup, but column 4 (Stock). You can also type the lookup value directly in quotes.
3. Approximate match: price bands
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Shipping | Parcel weight | 6.3 | |
| 2 | 0 | 4.99 | Shipping | 12 | |
| 3 | 2 | 7.5 | |||
| 4 | 5 | 12 | |||
| 5 | 10 | 20 |
With TRUE, VLOOKUP returns the row of the largest value that is less than or equal to 6.3, which is 5 → 12. The first column must be sorted from smallest to largest.
4. Show a message when nothing is found
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Stock | Product P-200 | |
| 2 | P-101 | Desk lamp | 24.5 | 40 | Not found | |
| 3 | P-102 | Office chair | 149 | 12 | ||
| 4 | P-103 | Monitor stand | 39.9 | 25 | ||
| 5 | P-104 | Keyboard | 59 | 0 |
P-200 isn't in the list, so VLOOKUP returns #N/A. IFERROR replaces the error with a friendly message.
Common mistakes
Leaving out the 4th argument. Without it, VLOOKUP uses an approximate match and can quietly return the wrong row.
Fix: Always add FALSE for exact matches:
=VLOOKUP(G1,A2:D5,3,FALSE).The value you search for isn't in the first column. VLOOKUP only searches the leftmost column and can't look to the left.
Fix: Start the table at the column you search in, or use XLOOKUP or INDEX with MATCH.
Counting the column number from column A. The number is counted from the start of the table, not of the sheet.
Fix: In a table B2:E10, column B is 1 and column E is 4.
The table moves when you copy the formula down. Each row then searches a shifted range and some results turn into #N/A.
Fix: Lock the table with $ signs:
=VLOOKUP(G1,$A$2:$D$5,3,FALSE)(press F4 after selecting the range).#N/A although the value is clearly there. Usually extra spaces, or a number stored as text in one place and as a number in the other.
Fix: Clean the data with TRIM, and make sure both sides are the same type.
Frequently asked questions
Why does VLOOKUP return #N/A?
The value wasn't found: it's missing, it isn't in the first column, or it differs slightly (spaces, text vs number). Wrap the formula in IFERROR to show your own message.
Can VLOOKUP look to the left?
No. It returns values only from columns to the right of the search column. XLOOKUP and INDEX/MATCH can return values from any column.
Should I use VLOOKUP or XLOOKUP?
Use XLOOKUP if everyone who opens the file has Excel 365 or Excel 2021 or newer. Use VLOOKUP when the file must work in older Excel versions.
Is VLOOKUP case-sensitive?
No. "p-103" and "P-103" are treated as the same value.
Related functions
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- INDEXINDEX returns the value at a given row and column of a range.
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.