VLOOKUP function in Excel

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

VLOOKUP is the function that most people learn first when one table has a code and another table has the details. The V stands for vertical: it searches down a column. Give it a price list and a product code and it brings back the price. Give it a staff list and an employee number and it brings back the name.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Argument	What it is
`lookup_value`	The value to look for. It is searched in the first column of the table.
`table_array`	The table to search. Its first column must hold the values you are looking up.
`col_index_num`	Which column of the table to return, counted from the left of the table. The first column is 1.
`range_lookup`	FALSE for an exact match, which is what you want nearly always. TRUE, or left out, for an approximate match on a sorted table.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

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
Looks for the code B201 in column A and returns the 2nd column of the table, the item name.
=VLOOKUP("C302",A2:E8,4,FALSE)
Result: 1299
Same idea, 4th column: the price of code C302.
=VLOOKUP("Z999",A2:E8,2,FALSE)
Result: #N/A
The code is not in the table, so the answer is the error #N/A, "not available".
=IFERROR(VLOOKUP("Z999",A2:E8,2,FALSE),"Not found")
Result: Not found
Wrapping the formula in IFERROR shows a friendly message in place of the error.

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
Approximate match: 91 marks falls in the band that starts at 80, so the grade is A.
=VLOOKUP(B3,$D$2:$E$6,2,TRUE)
Result: D
45 marks: the largest "From" value that is not more than 45 is 33, grade D.

How it works

VLOOKUP does two things. First it goes down the first column of the table until it finds the lookup value. Then it moves across that row to the column whose number you gave, and returns what it finds there.

The column number counts from the left edge of the table you selected, not from column A of the sheet. If the table is C5:F20, then column C is 1 and column F is 4.

Always type FALSE for an exact match

The fourth argument is the one that causes trouble. If you leave it out, Excel assumes TRUE, an approximate match, which only works on a table sorted in rising order and otherwise returns wrong answers without any error. For codes, names and ID numbers, always end the formula with FALSE. You may also type 0, which means the same.

Approximate match has one good use: bands. Tax slabs, grade boundaries, commission rates and courier charges by weight are all tables that say "from this value upwards". List the lowest value of each band in the first column, in rising order, and use TRUE. The last two examples above grade marks this way.

Lock the table with dollar signs

When you copy a VLOOKUP down a column, the table reference moves down with it unless you fix it. Write $A$2:$E$8 and it stays put. Pressing F4 while the cursor is on the reference adds the dollar signs for you. See the page on absolute and relative references.

What VLOOKUP cannot do

It cannot look to the left. The value you search must be in the first column of the table, and the answer must be to its right. To look left, use XLOOKUP or INDEX with MATCH.
It returns only the first match. If a code appears twice, you get the upper row.
Inserting a column breaks it. The column number is a fixed figure, so a new column inside the table makes the formula return the wrong column.

Why you get #N/A when the value is there

A number stored as text in one table and as a real number in the other. They look the same and do not match.
Spaces before or after the value. Clean them with TRIM.
The table reference slipped because it had no dollar signs.
The lookup column is not the first column of the selected table.

Capital and small letters do not matter to VLOOKUP: "b201" finds "B201".

In Google Sheets

VLOOKUP works the same way in Google Sheets, with the same four arguments.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

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