Why does VLOOKUP work for some rows but return #N/A for others?

Usually the range wasn't locked with $ and shifted as you filled down, or some keys came from a different export with spaces or text-numbers.

Why does XLOOKUP not find matches between two files?

The same causes as VLOOKUP apart from column position and default match mode: types, hidden spaces, decimals or genuinely missing keys. Test one pair with =A=B first.

Should I use IFERROR or IFNA?

IFNA. It only catches 'not found' and leaves other errors visible, so you don't hide a broken reference.

Is VLOOKUP case-sensitive?

No. It treats upper and lower case as equal, so case differences never cause #N/A. If you need case-sensitive matching, use EXACT inside XLOOKUP or INDEX/MATCH — see case-sensitive matching between two lists.

Why does my lookup return #N/A only after I sort the data?

Sorting doesn't break an exact-match lookup, but it does break VLOOKUP with approximate match if the list ends up unsorted, and it breaks formulas that point at fixed cells in the old order. Use exact match and whole-column or Table references.

VLOOKUP returns #N/A across sheets: every cause and its fix

By the Analistable team · Updated 2026-10-10 · 5 min read

#N/A means the lookup didn't find the value. Work through the causes in order: the key really is missing, the table range doesn't cover it or shifted when filled down, the key isn't in VLOOKUP's first column, approximate match was used by accident, the two keys differ in type (number vs text), or one has hidden spaces. Check each with a one-cell test before wrapping the formula in IFNA.

Part of our guide: How to join spreadsheets on a common column

Start with a one-cell test

Pick one row that returns #N/A but where you're sure the key exists in the other sheet. Find that key by eye, then test the two cells directly:

=F2=Lookup!A57

If that returns FALSE, the values are different — skip to types and spaces below. If TRUE, the values match and the problem is the formula: its range, column or match mode. This one test halves the search.

The checklist

Causes of #N/A in cross-sheet lookups
#	Cause	Test	Fix
1	Key genuinely missing	COUNTIF(Lookup!A:A, F2) = 0 and no near-duplicates	Not an error — handle with IFNA
2	Range too short	Last data row beyond the range end	Use whole columns or an Excel Table
3	Range shifted when filled down	Formula in row 20 shows A20:C520	Lock with $A$2:$C$500
4	Key not in first column (VLOOKUP)	Lookup column is B but range starts at A	Start range at key column, or use XLOOKUP
5	Approximate match by accident	Fourth argument missing or TRUE	Add FALSE (or 0)
6	Number vs text	ISTEXT differs between the two cells	VALUE, TEXT or Text to Columns
7	Hidden spaces	LEN differs from what you see	TRIM + SUBSTITUTE(CHAR(160))
8	Decimal differences	Subtracting the two gives non-zero	ROUND both sides
9	Wildcard characters in key	Key contains * or ? or ~	Escape with ~ or use XLOOKUP

Causes 1–3: the key is missing or out of range

If =COUNTIF(Lookup!A:A, F2) returns 0, the value isn't there — or isn't there in the same form. Look for near-matches with a wildcard: =COUNTIF(Lookup!A:A, "*"&F2&"*"). A hit means padding, prefixes or spaces; see fixing mismatched IDs before a join.

Ranges are the commonest formula fault. =VLOOKUP(F2, Lookup!A2:C500, 3, FALSE) filled down becomes A3:C501, A4:C502 and so on, so early rows fall out of later ranges. Use Lookup!$A$2:$C$500, a whole-column reference such as Lookup!$A:$C, or turn the source into an Excel Table (Ctrl+T) and use Table1 — tables grow automatically when data is added.

Causes 4–5: column position and match mode

VLOOKUP only searches the first column of the range. If your key is in column B, a range of A:D won't work: start the range at B, or use XLOOKUP or INDEX/MATCH, which take separate lookup and return ranges.

VLOOKUP's fourth argument defaults to TRUE — approximate match — if you leave it out. On an unsorted list that returns wrong values or #N/A seemingly at random. Always write FALSE (or 0). XLOOKUP defaults to exact match, so this cause doesn't apply unless you set match_mode to -1 or 1. MATCH defaults to 1 (approximate) too, so write MATCH(F2, Lookup!A:A, 0).

Causes 6–8: the values differ invisibly

Type: =ISTEXT(F2) and =ISTEXT(Lookup!A57) give different answers. Convert one side — full method in numbers stored as text stop lookups working.
Spaces: =LEN(F2) and =LEN(Lookup!A57) differ. Clean with =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) — see trailing spaces break lookups.
Decimals: =F2-Lookup!A57 isn't exactly 0. Round both sides — see lookups failing on decimals.

Case is not a cause: VLOOKUP, XLOOKUP and MATCH ignore upper and lower case, so "abc" finds "ABC".

Cause 9: wildcards in the key

VLOOKUP and MATCH treat * and ? as wildcards, and ~ as an escape, even in exact-match mode. A key like AB*12 can match the wrong row, and AB~12 won't match itself. Escape the characters: =VLOOKUP(SUBSTITUTE(SUBSTITUTE(F2,"~","~~"),"*","~*"), …) — or use XLOOKUP, which only uses wildcards when match_mode is 2.

Across files: extra checks

A lookup into another workbook keeps working when that file is closed — Excel uses the values saved with the link — but they're only as fresh as the last update. Data → Edit Links shows the source and its status. More in XLOOKUP from another workbook.
If the source file was moved or renamed you'll usually get #REF! or a prompt to update values, not #N/A.
In Google Sheets, VLOOKUP can't reference another spreadsheet directly. Wrap the range in IMPORTRANGE: =VLOOKUP(F2, IMPORTRANGE("url", "Sheet1!A:C"), 3, FALSE), and allow access the first time.
XLOOKUP returns #VALUE! rather than #N/A when the lookup and return arrays are different sizes.

Only then: handle genuine misses

Once the remaining #N/A rows are keys that really don't exist in the other sheet, label them rather than hiding them:

=IFNA(VLOOKUP(F2, Lookup!$A:$C, 3, FALSE), "not in price list")
=XLOOKUP(F2, Lookup!$A:$A, Lookup!$C:$C, "not in price list")

Avoid IFERROR here: it also hides #REF! and #VALUE!, which signal broken formulas. A labelled miss is a finding; count them with COUNTIF and you have your exception list.

If you'd rather not debug formulas at all, Analistable joins the sheets in SQL inside your browser and can tell you which keys from one file have no match in the other — trimming and casting keys as part of the query. The join spreadsheets guide and VLOOKUP vs XLOOKUP vs INDEX MATCH cover the formula options in more depth.

Frequently asked questions

Why does VLOOKUP work for some rows but return #N/A for others?: Usually the range wasn't locked with $ and shifted as you filled down, or some keys came from a different export with spaces or text-numbers.
Why does XLOOKUP not find matches between two files?: The same causes as VLOOKUP apart from column position and default match mode: types, hidden spaces, decimals or genuinely missing keys. Test one pair with =A=B first.
Should I use IFERROR or IFNA?: IFNA. It only catches 'not found' and leaves other errors visible, so you don't hide a broken reference.
Is VLOOKUP case-sensitive?: No. It treats upper and lower case as equal, so case differences never cause #N/A. If you need case-sensitive matching, use EXACT inside XLOOKUP or INDEX/MATCH — see case-sensitive matching between two lists.
Why does my lookup return #N/A only after I sort the data?: Sorting doesn't break an exact-match lookup, but it does break VLOOKUP with approximate match if the list ends up unsorted, and it breaks formulas that point at fixed cells in the old order. Use exact match and whole-column or Table references.