VLOOKUP function in Google Sheets

VLOOKUP finds a value in the first column of a range and returns a value from the same row in another column.

VLOOKUP fetches a detail from a table: the price for a code, the name for a roll number. It behaves in Google Sheets as it does in Excel, with a few extras that come from the way Sheets handles arrays.

Syntax

=VLOOKUP(search_key, range, index, [is_sorted])

Argument	What it is
`search_key`	The value to look for.
`range`	The table to search. The key is looked for in its first column.
`index`	Which column of the range to return, counting from 1.
`is_sorted`	FALSE for an exact match. TRUE, or left out, assumes the first column is sorted and returns the nearest match at or below the key.

Examples

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

=VLOOKUP("B201",A2:E8,2,FALSE)
Result: Notebook
The item for code B201: second column of the range.
=VLOOKUP("C302",A2:E8,4,FALSE)
Result: 1299
The price for code C302: fourth column.
=VLOOKUP("Z999",A2:E8,2,FALSE)
Result: #N/A
A code that is not in the list.
=IFNA(VLOOKUP("Z999",A2:E8,2,FALSE),"Not found")
Result: Not found
IFNA gives a message for a missing key.
=ARRAYFORMULA(VLOOKUP({"A102";"C301"},A2:E8,2,FALSE))
Result:
Toor Dal 1 kg
LED Bulb
Inside ARRAYFORMULA, VLOOKUP looks up a whole list of keys from one formula.
=VLOOKUP("D401",A2:E8,{2,4},FALSE)
Result:
Steel Bottle 450
A list of column numbers in curly brackets returns several columns at once.

Sample sheet: A marks table and a grade table. The grade table lists the lowest mark of each grade, in rising order.

	A	B	D	E
1	Name	Marks	From	Grade
2	Asha	91	0	E
3	Ravi	45	33	D
4	Meena	79	50	C
5	Kabir	28	65	B
6	Tara	65	80	A

=VLOOKUP(B2,$D$2:$E$6,2,TRUE)
Result: A
Sorted mode for bands: 91 marks falls in the band starting at 80.

How it works

VLOOKUP searches down the first column of the range for the key, then moves across to the column number you gave and returns that cell.

The fourth argument decides the kind of match. Type FALSE for codes, names and anything that must match exactly. If you leave it out, Sheets assumes the first column is sorted and returns the closest value at or below the key, which on unsorted data gives wrong answers without an error.

The sorted mode is right for bands: tax slabs, grade boundaries, delivery charges by weight. List the lowest value of each band in the first column, in rising order.

One formula for a whole column

In Sheets, VLOOKUP expands inside ARRAYFORMULA. Put this in the first row:

=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, Prices!A:D, 4, FALSE)))

It fills the whole column, and new rows are looked up as they are added. No copying down, and no dollar signs to forget.

Several columns at once

Give the index as a list in curly brackets, {2,4}, and VLOOKUP returns those columns side by side, as the sixth example shows. Excel's VLOOKUP needs a separate formula for each column.

Looking to the left

VLOOKUP's key must be in the first column of the range. Sheets offers a neat way round: build a range in the order you need with curly brackets.

=VLOOKUP("Notebook", {B2:B8, A2:A8}, 2, FALSE)

The braces make a two-column range with the names first and the codes second. Many people prefer XLOOKUP or INDEX with MATCH, which look in any direction.

A table in another file

The range can come from another spreadsheet through IMPORTRANGE: =VLOOKUP(A2, IMPORTRANGE(url, "Prices!A:D"), 4, FALSE). For many lookups, import the table once to a tab of its own and look it up there. It is much faster.

Why you get #N/A

The key is not in the first column.
A number on one side and digits stored as text on the other.
Spaces around a value. Clean with TRIM.
The range was not fixed with dollar signs and slid down when the formula was copied.

Capital and small letters are treated as the same.

Common mistakes

Leaving out FALSE.
Counting the index from column A of the sheet instead of from the first column of the range.
An index larger than the number of columns in the range, which gives #REF!.
Inserting a column inside the table, which shifts what the index points to.

In Excel

The same function with the same four arguments. Excel calls them lookup_value, table_array, col_index_num and range_lookup. The ARRAYFORMULA and curly-bracket tricks are particular to Sheets. Modern Excel spills a list of keys without a wrapper.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.