VLOOKUP vs XLOOKUP in Excel (And How to Stop the Errors)

Q: Why does my VLOOKUP return the wrong value?

Usually the missing fourth argument. Without FALSE , VLOOKUP does an approximate match and returns the nearest lower value rather than an error, which looks plausible and is wrong. The other common cause is a table range without $ signs that shifts as the formula is filled down.

Q: Should I use IFERROR or IFNA around a lookup?

IFNA . It catches only "not found", while IFERROR also hides #REF! and other errors that indicate a genuinely broken formula you would want to know about.

Use XLOOKUP if your Excel has it. It searches in any direction, returns an exact match by default, and lets you specify what to show when nothing is found: =XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found").

VLOOKUP still works and is worth knowing, because you will meet it in other people's spreadsheets. Its two traps: it can only look to the right of the search column, and if you forget the fourth argument it silently returns approximate matches.

Lookup functions are where most spreadsheet errors come from, and almost all of them trace to two things: the wrong match mode, and a range that shifts when you copy the formula down. This guide covers both functions, when each is right, and how to stop the errors.

XLOOKUP: the one to use now

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Only the first three are required:

=XLOOKUP(A2, Products!A:A, Products!C:C)

Read it as: find what is in A2 somewhere in the Products column A, and give me the matching value from column C. Two separate ranges, so nothing has to be counted.

Three things it fixes outright:

Exact match is the default. No fourth argument to forget.
It searches in any direction. The return column can be left of the lookup column, which VLOOKUP cannot do.
You control the not-found result. =XLOOKUP(A2, Products!A:A, Products!C:C, "Not found") shows your own text instead of #N/A.

It is available in Microsoft 365 and Excel 2021 onwards. If your version does not have it, you will get #NAME?, which tells you to use VLOOKUP instead. Microsoft's XLOOKUP reference documents the optional match and search modes.

VLOOKUP: what the arguments mean

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

Argument	Meaning	Trap
`lookup_value`	What you are searching for	Must be in the first column of the table
`table_array`	The block to search	Needs absolute references when filled down
`col_index_num`	Which column to return, counted from the left of the block	Breaks silently if a column is inserted
`range_lookup`	`FALSE` = exact, `TRUE` = approximate	Omitting it means `TRUE`

A correct one:

=VLOOKUP(A2, Products!$A$2:$D$500, 3, FALSE)

Two details are doing a lot of work there. The dollar signs lock the table range so it does not slide down as you fill the formula; without them, row 2 searches A2:D500, row 3 searches A3:D501, and the last rows quietly fall off the bottom of your data. And FALSE demands an exact match.

The approximate-match trap

This is the single most expensive mistake in spreadsheets, because it does not produce an error — it produces a plausible wrong number.

Leave off the fourth argument and VLOOKUP defaults to approximate matching. It then assumes your lookup column is sorted ascending and, when it cannot find an exact match, returns the value for the largest entry less than what you asked for.

Look up product code 1050 in a list containing 1000 and 1100, and you get the row for 1000. No error. No warning. A number that looks entirely reasonable in a report.

Always write FALSE unless you are deliberately doing banded lookups — tax brackets, grade boundaries, shipping tiers — which is the one case approximate matching is genuinely for, and where the lookup column must be sorted ascending.

Why VLOOKUP cannot look left

VLOOKUP searches the first column of the range you give it and counts rightwards. If the value you want sits to the left of the value you are searching, there is no column index that reaches it.

The old workaround was INDEX/MATCH:

=INDEX(Products!A:A, MATCH(A2, Products!C:C, 0))

Read inside out: MATCH finds the row number where A2 appears in column C, and INDEX returns whatever is on that row in column A. The 0 is MATCH's exact-match flag — the equivalent of VLOOKUP's FALSE.

INDEX/MATCH is still worth recognising, because it is everywhere in existing spreadsheets. For new work, XLOOKUP does the same job in one function.

Errors and what they mean

Error	Cause	Fix
`#N/A`	Value genuinely not found	Check spelling and stray spaces; wrap in `IFNA` if absence is expected
`#N/A` when it clearly exists	Text vs number mismatch, or trailing spaces	`TRIM()` both sides; check one is not text-formatted
`#REF!`	`col_index_num` exceeds the range width	Widen the range or reduce the index
`#NAME?`	Function does not exist in your Excel version	XLOOKUP needs 365 or 2021+; use VLOOKUP or INDEX/MATCH
Wrong value returned	Approximate match, or unlocked range	Add `FALSE`; add `$` to the range

The second row is the one that wastes the most time. A lookup value stored as text will not match the same digits stored as a number, and the two look identical on screen. Numbers stored as text are usually left-aligned by default while real numbers are right-aligned — that alignment difference is the quickest visual check.

To handle expected misses cleanly:

=IFNA(VLOOKUP(A2, Products!$A$2:$D$500, 3, FALSE), "Not found")

IFNA is better than IFERROR here, because IFERROR would also swallow a #REF! caused by a genuinely broken formula, hiding a real bug behind friendly text.

Which to use

Situation	Use
Excel 365 or 2021+	XLOOKUP
Older Excel, return column is right of lookup	VLOOKUP with `FALSE`
Older Excel, return column is left	INDEX/MATCH
Banded ranges (tax, grades)	VLOOKUP with `TRUE`, sorted ascending
Sharing with unknown Excel versions	VLOOKUP or INDEX/MATCH — XLOOKUP shows `#NAME?`

That last row matters if you send spreadsheets to other people. An XLOOKUP formula opened in Excel 2019 does not degrade gracefully; it fails.

Habits that prevent lookup errors

Always specify exact match — FALSE in VLOOKUP, 0 in MATCH. XLOOKUP does it for you.
Lock your ranges with $, or convert the source to a Table and reference it by name, which never shifts.
TRIM imported data. Trailing spaces from CSV exports are the most common cause of a lookup failing on a value you can see.
Sanity-check one row by hand. Pick a row, find the answer manually, compare. It takes a minute and catches an off-by-one column index immediately.

For unit conversion inside the same sheet, Excel has a dedicated function — our guide to converting units in Excel covers CONVERT and its case-sensitive unit codes. And if the numbers you are looking up need checking, our percentage calculator is a quick independent verification.

For the error codes themselves — #REF!, #VALUE!, #DIV/0! and the rest — see our guide to Excel formula errors explained, including how to trace one back to its source.

When a lookup returns #N/A on a value you can plainly see in the table, why VLOOKUP cannot find a match that exists gives the one-cell test that identifies the cause.

For banded results rather than a lookup table, Excel IF with multiple conditions covers IFS and nested IF — though past three or four bands, a lookup is usually the better answer.

Frequently asked questions

What is the difference between VLOOKUP and XLOOKUP?

XLOOKUP takes separate lookup and return ranges instead of a counted column index, defaults to exact matching, can search left as well as right, and lets you set a custom not-found value. VLOOKUP requires the lookup value in the first column, counts columns rightwards, and defaults to approximate matching.

Why does my VLOOKUP return the wrong value?

Usually the missing fourth argument. Without FALSE, VLOOKUP does an approximate match and returns the nearest lower value rather than an error, which looks plausible and is wrong. The other common cause is a table range without $ signs that shifts as the formula is filled down.

Why does VLOOKUP say #N/A when the value is clearly there?

Most often a type mismatch or invisible whitespace — one side is text and the other a number, or there are trailing spaces from an import. Wrap both sides in TRIM(), and check alignment: text is left-aligned by default, numbers right-aligned.

Can VLOOKUP look to the left?

No. It searches the first column of its range and counts rightwards. Use XLOOKUP, or INDEX/MATCH, both of which take the search and return ranges independently.

Why do I get #NAME? with XLOOKUP?

Your version of Excel does not have the function. XLOOKUP requires Microsoft 365 or Excel 2021 and later. Use VLOOKUP or INDEX/MATCH instead, which also makes the file safe to share with older versions.

Should I use IFERROR or IFNA around a lookup?

IFNA. It catches only "not found", while IFERROR also hides #REF! and other errors that indicate a genuinely broken formula you would want to know about.

Conclusion

Use XLOOKUP where you can, VLOOKUP with FALSE where you cannot, and lock your ranges either way. The approximate-match default is the one thing worth being genuinely careful about, because it fails without an error message.

Check one row by hand whenever you build a new lookup. It is the cheapest possible safeguard against a wrong column index quietly propagating through a thousand rows.