Home › Excel functions › VLOOKUP

VLOOKUP Formula in Excel: How to Use It, with Examples

The VLOOKUP formula finds a value in the first column of a table and returns a value from the same row. It's one of the most-used functions in Excel, for things like looking up a product's price, an employee's department or a customer's address.

Excel CSE · 7 min read

VLOOKUP syntax

VLOOKUP takes four arguments. The first three are required.

Step-by-step example

Say you have a small price list and you want Excel to tell you the price of a mango.

This formula returns $1.20. Here is what Excel does, step by step:

1. "Mango" is what it looks for. Text always goes between double quotes.
2. A2:C7 is the table it searches. Excel looks down the first column of that range (column A) until it finds "Mango" in row 5.
3. 3 tells Excel to return the value from the 3rd column of the range. Column A is 1, B is 2 and C is 3, so it returns the price.
4. FALSE means "only accept an exact match". If there were no mango in the list, you'd get #N/A instead of a wrong answer.

To get the stock of a mango instead, change the column number to 2: =VLOOKUP("Mango", A2:C7, 2, FALSE) returns 40.

Using a cell reference instead of typed text

Typing "Mango" into the formula works, but in practice you'll almost always point to a cell. That way you can change the input without touching the formula.

The formula in F2 is:

Type a different product into E2 and the price in F2 updates automatically. The dollar signs in $A$2:$C$7 make the table reference absolute, so it doesn't shift when you copy the formula down to other rows. Press F4 after selecting the range to add them quickly.

Combining VLOOKUP with a calculation

VLOOKUP returns a normal value, so you can use it inside a bigger formula. If cell G2 holds a quantity, this calculates the total cost:

Exact match vs. approximate match

The last argument changes how VLOOKUP searches, and getting it wrong is the source of many silent errors.

Exact match (FALSE)

Use FALSE (or 0) when you're looking up something specific: a product name, an ID, an email address. Excel either finds that exact value or returns #N/A. The table doesn't need to be sorted.

Approximate match (TRUE or omitted)

Use TRUE when you're looking up a value within ranges, such as tax brackets, discount tiers or grade boundaries. Excel finds the largest value that is less than or equal to your lookup value. For this to work, the first column must be sorted from smallest to largest.

In this example, each row is the minimum score for a grade:

There's no 73 in column A, so Excel takes the largest value that isn't higher than 73. That's 70, so the result is B.

Common VLOOKUP errors and how to fix them

#N/A: value not found

Excel couldn't find the lookup value in the first column. Check these causes in order:

• Typos or extra spaces. "Mango " with a trailing space is not the same as "Mango". Clean the data with =TRIM(A2).
• Numbers stored as text. The ID 1001 as a number doesn't match "1001" as text. Look for a small green triangle in the cell, or convert with =VALUE(A2). See convert text to numbers.
• Wrong first column. The value you're looking for must be in the leftmost column of table_array.
• The table reference moved. If you copied the formula down without dollar signs, the range shifted. Use $A$2:$C$7.
• The value really doesn't exist. Then #N/A is the correct answer. You can show a friendlier message with IFERROR (see the tips below).

#REF!: column number too high

Your col_index_num is larger than the number of columns in the range. =VLOOKUP(E2, A2:C7, 4, FALSE) fails because A2:C7 only has 3 columns.

#VALUE!: column number too low

The column number is less than 1, or not a number at all. The first column of the range is always 1.

Wrong result, but no error

This is the sneaky one. Usually you either left out FALSE (so Excel did an approximate match on unsorted data), or you counted the column number from column A instead of from the start of your range.

Tips for reliable lookups

• Show a friendly message instead of #N/A: =IFERROR(VLOOKUP(E2, $A$2:$C$7, 3, FALSE), "Not found")
• Search for partial text with wildcards. With an exact match, * matches any characters and ? matches one character. =VLOOKUP("Man*", A2:C7, 3, FALSE) finds "Mango".
• Use whole columns for growing lists. $A:$C includes new rows automatically. Even better, turn your data into a table (Ctrl+T) and use structured references.
• Watch out when inserting columns. The column number is a fixed number. If you insert a column inside the range, VLOOKUP keeps returning column 3, which is now a different column. XLOOKUP doesn't have this problem.
• Duplicates: VLOOKUP returns the first match from the top and ignores the rest.

VLOOKUP vs. XLOOKUP vs. INDEX/MATCH

VLOOKUP has been around for decades, but it has two well-known limitations: it can't look to the left, and the column number breaks when columns are inserted. Two alternatives solve this.

The same price lookup with XLOOKUP looks like this:

You point to the column to search and the column to return, so there's no column number to count. If everyone who uses your file has a recent version of Excel, XLOOKUP is usually the better choice. VLOOKUP is still worth mastering, because it works everywhere and you'll find it in countless existing spreadsheets.

VLOOKUP in other languages

If your Excel is set to another language, the function has a different name. Many European versions also use a semicolon (;) instead of a comma between arguments, because the comma is used as the decimal separator.

The separator depends on your system's regional settings, not only on the Excel language. If Excel rejects a formula with commas, try semicolons.