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])
ArgumentMeaning
lookup_valueWhat you are looking for, such as a product code in A2.
table_arrayThe table to search. The lookup value must be in its first column.
col_index_numWhich column of that table to return: 1 is the first column, 2 the second, and so on.
range_lookupFALSE for an exact match, TRUE (or blank) for an approximate match.
Rule of thumb: almost always finish VLOOKUP with 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:

ABC
1CodeProductPrice
2P100A4 Paper (box)95
3P101Stapler18
4P102Whiteboard 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 fromCommission
00%
10,0003%
25,0005%
50,0007%

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

ErrorUsual causeFix
#N/AThe 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 resultrange_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

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

Next: learn the modern replacement, XLOOKUP.