VLOOKUP in Google Sheets

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

In Google Sheets

In Google Sheets the arguments are called search_key, range, index and is_sorted, but they work as here. is_sorted defaults to TRUE, so write FALSE for an exact match, as in Excel.

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

Related functions