What is VLOOKUP?

VLOOKUP finds a value in a list and gives you something else from the same row.

That’s it. If you have a price list and you want to know what a Gadget costs, VLOOKUP is the function that goes and looks it up for you, instead of you scrolling down to find it by eye.

The V stands for vertical, because it searches down a column.

The example everybody actually has

A price list in columns A and B. The formula in the bottom row looks up whatever product name sits in the highlighted cell — change it and watch the price follow:

Try changing Gadget to Widget or Doohickey.

Product	Price
Widget	4.50
Gadget	12.00
Doohickey	7.25

Look up:	Gadget
Price:	12

Edit the highlighted cell and the formula recalculates — computed by VisiGrid engine 0.35.0, in your browser. Nothing is saved.

Type a product that isn’t on the list — Sprocket, say — and the formula returns #N/A, which is the subject of a later section.

The formula doing that work is:

=VLOOKUP("Gadget", A2:B4, 2, FALSE)

That returns 12.

In English: look for “Gadget” in the first column of A2:B4, and when you find it, give me whatever is in the second column of that same row.

Change the product and you get its price. Point it at a cell instead of typed text and it updates whenever that cell changes — which is the entire reason people use it rather than typing the price in by hand.

Reading the four parts

=VLOOKUP( "Gadget" , A2:B4 , 2 , FALSE )
           ↑          ↑       ↑    ↑
           what       where   which  how

Part	What it means
`"Gadget"`	What you are looking for
`A2:B4`	Where to look. Its first column is the one that gets searched
`2`	Which column to return, counting from the left of that range
`FALSE`	How to match — `FALSE` means “must match exactly”

The counting in the third part trips people up. It is not “column B” — it is “the second column of the range you just gave me.” If your range started at column B, then 2 would mean column C.

Two things follow from that first column being the one searched:

The range must start at the column you’re searching. Searching product names means starting at column A, where the names are.
You can only return columns to the right. VLOOKUP cannot look leftward at all — for that you need XLOOKUP or INDEX with MATCH.

The one rule worth memorising: always write FALSE

The fourth part is optional, and this is where nearly everyone gets burned.

Leaving it out does not mean “exact”. It means approximate, which lets VLOOKUP settle for the nearest value below the one you asked for — and return it without any error at all.

=VLOOKUP("Gadget", A2:B4, 2)          ← approximate
=VLOOKUP("Gadget", A2:B4, 2, FALSE)   ← exact. Write this one.

The cruel part is that the approximate version usually appears to work. On the table above, both return 12. It fails later, on different data, quietly, and by then the formula has been copied into forty other cells. You can see it actually going wrong here.

Write FALSE. Every time. It costs six characters.

When it says #N/A

#N/A means “not available” — VLOOKUP searched and didn’t find it:

=VLOOKUP("Sprocket", A2:B4, 2, FALSE)     → #N/A

There is no Sprocket in the list, so there is no price to return. That’s the function working correctly.

To show something friendlier instead, wrap it in IFERROR:

=IFERROR(VLOOKUP("Sprocket", A2:B4, 2, FALSE), "not stocked")     → not stocked

Use that with some care — IFERROR hides every error, including the ones telling you your range is broken.

If you’re getting #N/A on something that is definitely in the list, it’s usually a stray space or the range starting at the wrong column. The six usual causes cover almost all of it.

Should you learn VLOOKUP at all?

Honestly: learn XLOOKUP for your own new work. It does the same job without the column counting, searches in either direction, matches exactly by default, and handles “not found” without a wrapper. Nearly everything above that you’d have to memorise, it simply removes.

But learn to read VLOOKUP anyway, because:

It’s in almost every spreadsheet written before 2020, and you will inherit one
XLOOKUP doesn’t exist in Excel 2019 or earlier, so files meant for older versions still need it
Interviewers still ask about it

The full comparison is on XLOOKUP vs VLOOKUP, including the one situation where VLOOKUP is still the right answer.

Try it

Every formula on this page was run before publishing, not copied from documentation. If you want to try them yourself, the VLOOKUP reference has the full argument list, and you can paste any of them into a browser tab — no account, no download — at app.visigrid.app/try.

Common questions

What does VLOOKUP actually do?

It searches for a value down the first column of a range, and when it finds it, returns a value from the same row in a column you choose. The V stands for vertical, meaning it searches down a column rather than across a row.

Is VLOOKUP hard to learn?

The idea takes about five minutes — find this, give me that from the same row. The difficulty is entirely in its four arguments, particularly the fourth one, which does the opposite of what most people expect when left out.

Why do people always write FALSE at the end?

Because leaving it out switches VLOOKUP to approximate matching, which can return a value from the wrong row without showing an error. FALSE means exact match, which is what you want almost every time.

What does

It means the value you searched for was not found in the first column of your range. Common causes are a typo, a stray space, or a range that does not start at the column you are searching.

Should I learn VLOOKUP or XLOOKUP?

Learn XLOOKUP for your own new work — it is simpler and safer. Learn to read VLOOKUP anyway, because it is in almost every spreadsheet written before 2020 and in every file that must open in older Excel.

Can VLOOKUP search a column to the right and return one to the left?

No. VLOOKUP only searches the leftmost column of its range and only returns columns to the right of it. To go leftward you need XLOOKUP, or INDEX combined with MATCH.