VLOOKUP in Excel: Formula, Examples and Common Errors
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021 and Excel 2019
VLOOKUP is one of the most used functions in Excel. It searches for a value in the first column of a table and returns a value from another column in the same row. Use it to pull a price from a price list, a customer's phone number from a contact sheet or an employee's department from an HR list, without copying and pasting by hand.
VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Meaning |
|---|---|
| lookup_value | What you are looking for, such as a product code in A2. |
| table_array | The table to search. The lookup value must be in its first column. |
| col_index_num | Which column of that table to return: 1 is the first column, 2 the second, and so on. |
| range_lookup | FALSE for an exact match, TRUE (or blank) for an approximate match. |
FALSE. If you leave it blank, Excel uses approximate match and can return a wrong answer without any warning.Exact match example: find a price
Your price list is on a sheet named Prices:
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P100 | A4 Paper (box) | 95 |
| 3 | P101 | Stapler | 18 |
| 4 | P102 | Whiteboard Marker (pack) | 22 |
On your order sheet, the code is in A2. To show the price in C2:
=VLOOKUP(A2, Prices!$A$2:$C$200, 3, FALSE)
Excel looks for the code in column A of the price list and returns the value from the 3rd column, Price. Change the 3 to 2 to return the product name instead.
Approximate match: bands and tiers
Approximate match is useful when you want the band a number falls into, such as a commission rate or a grade. Set up a table with the lower limit of each band, sorted from smallest to largest:
| Sales from | Commission |
|---|---|
| 0 | 0% |
| 10,000 | 3% |
| 25,000 | 5% |
| 50,000 | 7% |
If this table is in F2:G5 and a salesperson's total is in B2:
=VLOOKUP(B2, $F$2:$G$5, 2, TRUE)
Sales of 31,400 return 5%, because 25,000 is the largest limit that is not greater than 31,400. The first column must be sorted ascending or results will be wrong.
Copying the formula down
When you copy a lookup formula down a column, the lookup value should move (A2, A3, A4…) but the table should stay fixed. Lock the table with dollar signs by selecting the range in the formula and pressing F4: $A$2:$C$200. Even better, convert the price list to an Excel Table named tblPrices and use =VLOOKUP(A2, tblPrices, 3, FALSE); the table reference never shifts and grows automatically.
Fixing common VLOOKUP errors
| Error | Usual cause | Fix |
|---|---|---|
| #N/A | The value is not in the first column, or has an extra space, or is a number stored as text. | Check spelling, use TRIM, convert text to numbers. |
| #REF! | col_index_num is larger than the number of columns in table_array. | Widen the table range or lower the number. |
| #VALUE! | col_index_num is less than 1, or the lookup value is over 255 characters. | Use a column number of 1 or more. |
| Wrong result | range_lookup left blank on unsorted data. | Add FALSE. |
To show a friendly message instead of #N/A, wrap the formula in IFERROR:
=IFERROR(VLOOKUP(A2, tblPrices, 3, FALSE), "Code not found")
Use this only once you are sure the formula itself is correct, otherwise it hides real mistakes.
Partial matches with wildcards
With exact match, you can use * for any characters and ? for a single character. To find the first product whose name contains the word in E2:
=VLOOKUP("*"&E2&"*", Prices!$B$2:$C$200, 2, FALSE)
Limitations and better alternatives
- It cannot look left. The value you search for must be in the first column of the range.
- Column numbers break. Inserting a column inside the table changes which column "3" refers to.
- Only the first match is returned.
If you have Excel 2021 or Microsoft 365, XLOOKUP solves all three problems. In older versions, the INDEX and MATCH combination can look in any direction:
=INDEX(Prices!$C$2:$C$200, MATCH(A2, Prices!$A$2:$A$200, 0))
Summary
- VLOOKUP finds a value in the first column and returns a value from another column.
- End with
FALSEfor exact matches; useTRUEonly for sorted bands. - Lock the table with F4 or use an Excel Table.
- Use IFERROR for a clean message, and switch to XLOOKUP when you can.
Next: learn the modern replacement, XLOOKUP.