The VLOOKUP function

Looks for a value in the first column of a table and returns the value from another column in the same row.

How to use VLOOKUP

VLOOKUP works like a phone book: you know the name, it finds the row and reads off the number next to it. In a spreadsheet it searches down the first column of a table for your value, then returns something from the same row, from the column you choose.

Reach for it when two lists share a key. A sales list has product codes and you want the price from the price list. A staff list has employee numbers and you want the department. An invoice has a customer ID and you want the name. Write the formula once, fill it down, and every row gets its own answer.

The four arguments say what to find, where to look, which column to return and how exactly to match. The table must start with the column that holds the key, and the column number counts from the left edge of the table, not from column A. For an exact match, end with FALSE, as in =VLOOKUP(A2,Prices!$A$2:$C$100,3,FALSE). VLOOKUP only returns columns to the right of the key. When you need to look left, or want a formula that survives inserted columns, use XLOOKUP or INDEX with MATCH. Wrap it in IFERROR when you would rather see a blank or a message than #N/A.

Syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value
The value to find in the first column.
table_array
The table, starting with the column to search.
col_index_num
Which column of the table to return, counting from 1.
range_lookup (optional)
FALSE for an exact match. TRUE or left out finds the closest smaller value in a sorted column.

Examples

D1=VLOOKUP("Pear",A2:B4,2,FALSE)
ABCD
1FruitPrice6
2Apple4
3Pear6
4Plum5

In plain words: Find Pear in the first column of A2:B4 and return the value in the table's second column, only on an exact match.

D1=VLOOKUP("Kiwi",A2:B4,2,FALSE)
ABCD
1FruitPrice#N/A
2Apple4
3Pear6
4Plum5
E2=VLOOKUP(TRIM(D2),A2:B4,2,FALSE)
ABCDE
1FruitPriceSearch
2Apple4Pear 6
3Pear6
4Plum5

In plain words: Find the fruit in D2 without extra spaces in the first column of A2:B4 and return the value in the second column, only on an exact match.

Common VLOOKUP mistakes

  • VLOOKUP returns #N/A even though the value is in the list

    The two values look the same but are not. Usually there is a stray space after the text, as in "Pear ", or one side is a number and the other is text. Clean the lookup value with =VLOOKUP(TRIM(D2),A2:B4,2,FALSE). If a number is stored as text, VALUE(D2) turns it into a real number.

  • VLOOKUP returns a wrong price and shows no error

    The fourth argument is missing, so VLOOKUP does an approximate match and takes the nearest smaller value, which only makes sense in a sorted list. Add FALSE as the fourth argument: =VLOOKUP(A2,$F$2:$G$20,2,FALSE).

  • The formula works in the first row but gives #N/A further down

    When you fill the formula down, the table reference moves down with it, and sooner or later the key falls outside it. Lock the table with dollar signs, as in $F$2:$G$20. Select the range in the formula and press F4 to add them.

  • I need a column that sits to the left of the key

    VLOOKUP can only return columns to the right of the one it searches. Use =INDEX(A2:A20,MATCH(E2,B2:B20,0)) or =XLOOKUP(E2,B2:B20,A2:A20) instead, or move the key column to the front of the table.

Practice VLOOKUP

Read the guide

Questions

Why is my VLOOKUP not working?

Check five things in order. The fourth argument should be FALSE for an exact match. The value must be in the first column of the table. The column number counts from the table's first column, not from column A. The lookup value and the list must match exactly, so look for extra spaces, which TRIM removes, and numbers stored as text. And if you copied the formula down, lock the table with $, as in $A$2:$C$5.

How do I use VLOOKUP with multiple criteria?

VLOOKUP takes one lookup value, so join the criteria. Add a helper column in front of the table with =B2&"|"&C2, and look up E2&"|"&F2 in it. XLOOKUP needs no helper column, =XLOOKUP(1,(A2:A6=F1)*(B2:B6=F2),C2:C6) returns the row where both match.

How do I VLOOKUP from another sheet?

Point the table at the other sheet, as in =VLOOKUP(A2,Prices!$A$2:$C$100,3,FALSE). Click the other sheet's tab while you write the formula, and Excel fills in the sheet name for you.

How do I hide #N/A in VLOOKUP?

Wrap the formula in IFNA, as in =IFNA(VLOOKUP(A2,$F$2:$G$20,2,FALSE),"Not found"). IFNA only catches #N/A, so other errors, such as a wrong range, still show. IFERROR hides every error, so use it only when you are sure.

How do I return several columns with VLOOKUP?

VLOOKUP returns one column per formula. Type the column numbers 2, 3 and 4 in B1:D1, then use =VLOOKUP($A2,$F$2:$I$20,B$1,FALSE) in B2 and fill it right and down. XLOOKUP does it in one formula, =XLOOKUP(A2,F2:F20,G2:I20) spills all three columns.

Related functions