Excel practice › Excel functions › VLOOKUP

VLOOKUP in Excel: how it works, with examples you can edit

VLOOKUP finds a value in the first column of a table and returns the value from another column of the same row. To get the price of product 1004 from a list in A2:D6, where price is the third column: =VLOOKUP(1004, A2:D6, 3, FALSE).

The last argument matters more than it looks: FALSE asks for an exact match. Leave it out and Excel returns the closest smaller value when the one you asked for is missing, without any warning.

Skip to the 3 exercises ↓

Syntax

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

lookup_value
What to look for: a value or, more often, the cell that holds it.
table_array
The table to search. VLOOKUP only searches its first column, so the range has to start at the column that holds the lookup values.
col_index_num
Which column of the table to return, counted from the table's first column (1). It is a position inside table_array, not a sheet column letter.
range_lookup
FALSE = exact match. TRUE, or nothing at all, = approximate match, which needs the first column sorted ascending. Optional, and the default is the risky one.
Live examplePrice of the ID in F2
G2fx
ABCDEFG
1IDProductPriceStockFind IDPrice
21001Keyboard4912100465
31002Mouse1940
41004Headset650
51005Webcam387
61007Dock1203

How Excel reads it

  1. It takes the value in F2 (1004) and goes down the first column of A2:D6 until it finds it.
  2. In that row it counts columns from the left edge of the table: ID is 1, Product is 2, Price is 3, Stock is 4.
  3. It returns what's in column 3 of that row: 65.

The three ways it goes wrong

Each of these is live below. Fix the formula in the box and watch the cell.

1. The value you're looking for isn't in the table's first column. VLOOKUP never looks left, and never looks in the middle of the table. Searching A2:D6 for a product name fails, because column A holds IDs:

Live example#N/A: looking in the wrong column
G2fx
ABCDEFG
1IDProductPriceStockFind productPrice
21001Keyboard4912Mouse#N/A
31002Mouse1940
41004Headset650
51005Webcam387
61007Dock1203

2. The column number is bigger than the table. A table of three columns has no column 4, so Excel answers #REF!. This happens when the table range is narrower than you think, or after someone deletes a column.

Live example#REF!: column 4 of a 3-column table
G2fx
ABCDEFG
1IDProductPriceStockFind IDStock
21001Keyboard49121005#REF!
31002Mouse1940
41004Headset650
51005Webcam387
61007Dock1203

3. The last argument is missing. Without FALSE, VLOOKUP does an approximate match: if the value isn't there, it settles for the largest one below it. ID 1003 doesn't exist in this list, yet the formula returns a price, the one for 1002:

Live exampleNo FALSE: a wrong answer with no error
G2fx
ABCDEFG
1IDProductPriceStockFind IDPrice
21001Keyboard4912100319
31002Mouse1940
41004Headset650
51005Webcam387
61007Dock1203

Approximate match isn't a bug. It's the right tool for brackets, like a commission rate by sales tier or a grade by score, where you want "the row at or below this number". For looking up IDs, names or codes, always write FALSE.

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.

Exercise 1Stock of one product

Someone types a product ID in F2. In G2, return how many of that product are in stock.

G2fx
ABCDEFG
1IDProductPriceStockFind IDStock
21001Keyboard49121005
31002Mouse1940
41004Headset650
51005Webcam387
61007Dock1203
Show the answer

=VLOOKUP(F2,A2:D6,4,FALSE) gives 7.

Stock is the 4th column of A2:D6, and FALSE makes sure only an exact ID counts.

Exercise 2A missing ID

ID 1003 isn't in the list, so a plain VLOOKUP shows #N/A. In G2, return the product name for the ID in F2, or the text Not found when the ID doesn't exist.

G2fx
ABCDEFG
1IDProductPriceStockFind IDProduct
21001Keyboard49121003
31002Mouse1940
41004Headset650
51005Webcam387
61007Dock1203
Show the answer

=IFERROR(VLOOKUP(F2,A2:D6,2,FALSE),"Not found") gives Not found.

IFERROR returns the VLOOKUP's result when there is one, and your text when the lookup ends in an error. IFNA works the same way and only catches #N/A, which is safer: other errors stay visible.

Exercise 3VLOOKUP inside a calculation

An order line: the ID is in F2 and the quantity in G2. In H2, calculate the order value: the product's price times the quantity.

H2fx
ABCDEFGH
1IDProductPriceStockOrder IDQtyValue
21001Keyboard491210013
31002Mouse1940
41004Headset650
51005Webcam387
61007Dock1203
Show the answer

=VLOOKUP(F2,A2:D6,3,FALSE)*G2 gives 147.

A VLOOKUP returns a value like any other: once it gives 49, you can multiply it, compare it or pass it to another function.

Questions people ask

Why does VLOOKUP return #N/A when the value is in the table?

Almost always because the two values only look the same: one is a number and the other is text that looks like a number, or one has a space at the end. The other usual cause is a table range that doesn't start at the column being searched. VLOOKUP not working goes through each cause with a formula to fix.

Can VLOOKUP return a value from a column to the left?

No. It searches the first column of the table and returns from that column or one to its right. For a lookup to the left, use INDEX with MATCH or XLOOKUP.

Should I use VLOOKUP or XLOOKUP?

XLOOKUP is easier to get right: exact match is its default, it can look in any direction, and it doesn't break when a column is inserted. It isn't available in Excel 2016 or Excel 2019, though, and VLOOKUP is. If you share files with people on older versions, or don't know which Excel a test will use, know both.

What does the 0 at the end of some VLOOKUP formulas mean?

The same as FALSE: exact match. =VLOOKUP(F2,A2:D6,3,0) and =VLOOKUP(F2,A2:D6,3,FALSE) are the same formula.

Keep going

Lesson 3 is six more VLOOKUP drills on a coffee-shop menu: column numbers, the first-column rule, IFERROR, locking the table with $ before copying a formula down, and VLOOKUP inside IF. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

XLOOKUP function, with live examplesINDEX MATCH, with live examplesIFERROR and IFNA, with live examplesVLOOKUP not working: causes and fixes#N/A error in Excel: meaning and fixesVLOOKUP practice exercises (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: VLOOKUP function, Microsoft Support: XLOOKUP function. Last checked November 2026.