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
=VLOOKUP(1"Pear",2A2:B4,32,4FALSE)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | 6 | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
In plain words: Find 1Pear in the first column of 2A2:B4 and return the value 3in the table's second column, 4only on an exact match.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | #N/A | |
| 2 | Apple | 4 | ||
| 3 | Pear | 6 | ||
| 4 | Plum | 5 |
=VLOOKUP(1TRIM(D2),2A2:B4,32,4FALSE)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Fruit | Price | Search | ||
| 2 | Apple | 4 | Pear | 6 | |
| 3 | Pear | 6 | |||
| 4 | Plum | 5 |
In plain words: Find 1the fruit in D2 without extra spaces in the first column of 2A2:B4 and return the value 3in the second column, 4only 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.