Excel VLOOKUP Function — How to Use It, and the 4th Argument That Breaks It

TL;DR — VLOOKUP searches for a value down the leftmost column of a table and returns something from a column to its right: =VLOOKUP(lookup_value, table, col_index_num, [range_lookup]). The one setting that decides whether it works is the fourth argument: use FALSE (exact match) almost every time. Left it off or set TRUE? Excel does an approximate match that needs the first column sorted ascending and will silently return the wrong row on unsorted data. Two hard limits follow from its design: VLOOKUP can only look rightward, and its column number is a hard-coded integer that breaks the moment you insert a column. Works in all versions; XLOOKUP fixes both limits in Excel 365.

=VLOOKUP(A2, Products, 3, FALSE)    ' find A2 in col 1, return col 3, exact match
=VLOOKUP("APX-100", $E$2:$H$500, 4, FALSE)   ' lock the table with $ when you drag

VLOOKUP is the most-used lookup function in the world, and almost every problem people hit with it traces back to a single fact about how it works: it does not search the table. It scans one column — the leftmost — from top to bottom, and when it finds your value it steps a fixed number of columns to the right. Once you hold that picture, the exact-match trap, the "can't look left" wall, and the broken column number all stop being mysteries.

What you'll learn

The mental model: VLOOKUP scans the first column only, then counts right
The #1 bug: the fourth argument, and why it must be FALSE
The wall: VLOOKUP can't look to the left — and what to do instead
The silent breaker: a hard-coded column number that dies when you insert a column
What #N/A really means, and how to fix (not just hide) it
The one time approximate match is actually right: banded lookups

The mental model: it scans the first column, then counts right

Picture your table as a grid. VLOOKUP puts its finger at the top of the leftmost column and slides down until it finds lookup_value. That's the only column it ever searches. When it lands on a match, it moves its finger right by col_index_num columns and reads whatever is there.

=VLOOKUP(A2, Products, 3, FALSE)
'          |       |     |   |
'          |       |     |   +-- exact match
'          |       |     +------ return the 3rd column of the table
'          |       +------------ the table (col 1 is where it searches)
'          +-------------------- the value to find, in col 1

So two things are baked in from the start. The value you search for must sit in the first column of the table you hand it, and the number 3 is counted from that first column, not from column A of the sheet. Everything below is a consequence of this one design.

The #1 bug: the fourth argument (get it wrong and it lies)

range_lookup is optional, and that is exactly the trap. Leave it out and Excel defaults to TRUE — an approximate match:

=VLOOKUP(A2, Products, 3)          ' DANGER — defaults to approximate match
=VLOOKUP(A2, Products, 3, TRUE)    ' same thing, spelled out
=VLOOKUP(A2, Products, 3, FALSE)   ' RIGHT for 99% of jobs — exact match

Approximate match assumes the first column is sorted ascending and returns the row with the largest value less than or equal to your lookup value. On an unsorted product list, that's a random-looking wrong answer — and there is no error, no warning. The formula returns a number; it's just the wrong one. This single omission is the most common VLOOKUP bug in existence.

The habit that saves you: always type FALSE (or 0) as the fourth argument unless you have a specific reason not to. Exact match finds the row you meant or returns #N/A — an honest "not found" beats a silent wrong number every time.

The wall: VLOOKUP can only look to the right

Because it searches the first column and counts rightward, the value you return must be to the right of the value you search for. Want to look up a product ID and return the name in a column to its left? VLOOKUP simply cannot do it — there is no negative column number.

' Table: A = Name, B = ID.  You have the ID, you want the Name (to the LEFT).
=VLOOKUP(F2, A:B, ... )   ' impossible — Name is left of ID, no col number works

This isn't a limitation you work around by being clever — it's the reason INDEX + MATCH and later XLOOKUP exist. Both search and return independent ranges, so the direction stops mattering. If you find yourself rearranging columns just to make VLOOKUP happy, that's the signal to switch tools, not to move data.

The silent breaker: a hard-coded column number

col_index_num is a literal integer, and it has no idea which column you actually meant. Write VLOOKUP(A2, Data, 4, FALSE) and it will always return the 4th column — even after you insert a new column into the middle of Data, which shoves your target to position 5. The formula doesn't error. It quietly returns the wrong column.

This is the number-one reason an inherited workbook "suddenly breaks" after someone edits the source table. Two fixes:

' Fix 1 — let MATCH find the column by header, so it self-adjusts
=VLOOKUP(A2, Data, MATCH("Price", Data_Headers, 0), FALSE)

' Fix 2 — abandon the column number entirely with INDEX/MATCH or XLOOKUP
=INDEX(Data_Price, MATCH(A2, Data_ID, 0))

Wrapping MATCH inside VLOOKUP turns the brittle integer into a header lookup that survives inserted columns. It's a stopgap; the real cure is a function that never needed a column count in the first place.

What `#N/A` really means — and how to fix it

#N/A from VLOOKUP means one thing: the value wasn't found in the first column. With exact match, that's usually one of these:

Hidden spaces or characters — "APX-100 " won't match "APX-100". Clean the key with TRIM (or SUBSTITUTE for stray non-breaking spaces) on both sides.
Number-stored-as-text mismatch — the lookup value is the text "1001" but the table holds the number 1001 (or vice versa). They never match. Fix the type, don't paper over it.
The value genuinely isn't in the first column — check you pointed the table at the right starting column.

To show a friendly message instead of the raw error, wrap it — but wrap it tightly, so you only catch the not-found case, not real formula mistakes:

=IFERROR(VLOOKUP(A2, Products, 3, FALSE), "Not found")

Reach for IFERROR only once the formula is correct. A blanket IFERROR around a broken VLOOKUP hides the bug that's making it fail — you'll ship "Not found" for rows that should have matched.

The one time approximate match is right: banded lookups

There is a legitimate use for TRUE: band or bracket lookups, where you want the row a value falls into rather than an exact hit — tax brackets, commission tiers, grade boundaries, shipping-weight bands.

' Grade bands: 0->F, 60->D, 70->C, 80->B, 90->A  (first column sorted ascending!)
=VLOOKUP(B2, Grade_Table, 2, TRUE)    ' 73 -> lands in the 70 band -> "C"

Here approximate match is doing exactly what you want: find the largest threshold not exceeding the score. The non-negotiable condition is that the first column is sorted ascending — otherwise it breaks in the silent, unsorted-data way from earlier. This is the only scenario where leaving FALSE off is a feature, not a bug.

The judgment call: VLOOKUP or something newer?

VLOOKUP is fine, and everywhere, for the simple case: exact match, return value is to the right, columns won't move. Reach past it when:

You need to look left, or search and return in either direction → INDEX + MATCH or XLOOKUP.
The source table's columns get edited → anything that doesn't hard-code a column number.
You want a built-in "not found" value or to match on multiple criteria → XLOOKUP.

VLOOKUP isn't wrong; it's just the oldest tool in a drawer that now has better ones. Knowing its three weak points — the fourth argument, the rightward-only wall, and the fragile column number — is what lets you use it safely and know when to stop.

How ExcelMaster helps

Most "my VLOOKUP is wrong" reports aren't formula-syntax problems — they're the fourth argument defaulting to approximate, a lookup value that's text on one side and a number on the other, or a column number that drifted after an edit. ExcelMaster writes the lookup with exact match locked in, spots the type mismatch that's causing your #N/A, and — when the layout calls for a left-lookup or a table that keeps changing — reaches for INDEX/MATCH or XLOOKUP instead of forcing VLOOKUP to do a job it was never built for.

Frequently asked questions

How do I use VLOOKUP in Excel?

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE). Put the value you're searching for as the first argument, the table (whose first column contains that value) as the second, the column number to return (counted from the table's first column) as the third, and FALSE for an exact match as the fourth. Example: =VLOOKUP(A2, Products, 3, FALSE) finds A2 in the first column of Products and returns the third column.

Why is my VLOOKUP returning #N/A?

#N/A means the value wasn't found in the table's first column. The usual causes are hidden spaces (clean with TRIM), a number-stored-as-text mismatch between the lookup value and the table, or the value simply not being in the first column. Fix the underlying cause rather than hiding it with IFERROR.

What does the fourth argument in VLOOKUP do?

It sets the match type. FALSE (or 0) means exact match — use this almost always. TRUE (or omitted) means approximate match, which requires the first column sorted ascending and returns the largest value less than or equal to your lookup value. Approximate match is only correct for banded lookups like tax brackets or grade boundaries.

Can VLOOKUP look to the left?

No. VLOOKUP searches the leftmost column and can only return columns to its right. To look up a value and return something to its left, use INDEX/MATCH or XLOOKUP, which handle either direction.

Why did my VLOOKUP break after I inserted a column?

Because col_index_num is a fixed number. Inserting a column into the table shifts your target, but the number still points at the old position. Use MATCH("Header", …, 0) in place of the literal number so it self-adjusts, or switch to INDEX/MATCH or XLOOKUP, which don't count columns.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-05.