How to Fix VLOOKUP Returning N/A in Google Sheets

#N/A from VLOOKUP almost always means one thing: the formula looked, and it did not find a match. The value you searched for and the value in the lookup column are not identical — even when they look identical on screen.

The short answer

The most common cause is a value that looks right but isn't: an extra space, or a number stored as text. Before you rewrite anything, put the lookup value and the column value side by side and compare them as cleaned text. Wrap each in TRIM() in a spare cell and check whether they finally match. If you are matching IDs or codes, also confirm both are the same type — text vs number is invisible but fatal to a lookup.

If this is one of several things breaking in the same file, start with the broader Google Sheets troubleshooting guide, then come back to this specific repair.

Quick diagnosis#

Identify which layer failed before you touch the formula. Read the middle column first — don't jump from symptom straight to a fix you saw in a forum.

What you see	Likely cause	Safest first fix
#N/A for an obvious match	Extra spaces or text/number mismatch	Compare cleaned values side by side
Wrong result, not an error	Approximate match on an unsorted range	Use exact match for business records
Formula can't see the lookup column	Lookup column isn't first in the selected range	Move the range start, or switch formula pattern
Column index error	Return column is outside the selected range	Count the columns inside your table range
Error hidden, nothing to debug	A wrapping IFERROR is masking the real failure	Temporarily remove IFERROR while you debug

Protect the file before you change a formula#

Make a duplicate or version-safe copy first. You don't need a backup ritual — you need one clean rollback point before you touch formulas, source lists, or dashboard ranges.

Then write the symptom in one sentence: what changed, where, and what the sheet should have shown instead. That sentence keeps you from treating a data problem like a formula problem.

Check the lookup value first — spaces and text/number mismatches#

This is the cause behind most "but it's clearly there!" cases. The match is failing because the two values aren't truly equal.

Hidden spaces. A trailing space on INV-1042 won't match INV-1042. Compare both with TRIM() in a spare cell to see if they line up once the space is gone.
Number stored as text. A customer ID typed as 00417 may be text in one column and a number in the other. They display the same and never match.

Formula

=VLOOKUP(TRIM(A2), Sheet2!A:C, 3, FALSE)

TRIM(A2) strips stray spaces off the value you're searching for, so a clean ID matches a clean ID. FALSE forces an exact match — the right setting for invoices, IDs, and customer records. Fix the underlying data where you can; TRIM is a patch, not a substitute for clean input.

Use exact match for business records#

If VLOOKUP returns the wrong value instead of #N/A, you're probably running an approximate match on data that isn't sorted for it.

Approximate match (TRUE, or leaving the last argument blank) assumes the lookup column is sorted and returns the closest value below your search term. For invoices, customer IDs, or product codes, that "closest" answer is just wrong. Set the last argument to FALSE so the formula only returns a value when it finds an actual match — and shows #N/A honestly when it doesn't.

Make the lookup column the first column of the range#

VLOOKUP only searches the first column of the range you hand it. If your IDs sit in column C but your range starts at A, the formula is searching column A and will never find them.

Two safe fixes:

Start the range on the lookup column. Change A:F to C:F so column C is searched.
Switch to a pattern that doesn't care about column order — INDEX/MATCH, or XLOOKUP if you'd rather move off VLOOKUP entirely. See VLOOKUP vs XLOOKUP in Google Sheets for when that swap is worth it.

To pinpoint exactly which referenced cell is at fault before you rebuild anything, the guide on finding which cell is breaking a Google Sheets formula is the safer path.

Count the columns when you hit an index error#

VLOOKUP's third argument is a column number, counted from the start of your selected range — not a spreadsheet column letter. Ask for column 4 of a range that's only 3 columns wide and the lookup fails.

Count across your actual range, left to right, starting at 1. If the value you want lives in the third column of the range, the index is 3. Widen the range or correct the number to match.

Pull off IFERROR before you debug#

IFERROR makes a sheet calmer for the person reading it — but it also hides the broken cell you need to fix. If a formula returns a blank or a tidy message and you can't tell why, a wrapping IFERROR is probably masking the real #N/A.

Strip it temporarily, see the true error, fix the cause, then put the friendly message back as the last polish step. For business files, keep a debug copy where errors stay visible.

Don't do this

Don't rewrite working formulas or delete rows because one cell shows #N/A. The failure is upstream — in the value, the range, or the match type — not in the visible output. Fix the one input that doesn't match, and leave protected ranges and other formulas alone.

If the sheet came from a shared template#

A copied template carries inherited settings. Filters, protected ranges, and views can change what you see without changing the data underneath — so the row you think you're matching may not be the row VLOOKUP sees.

If the #N/A appeared right after copying a template, compare it against a clean original before rebuilding. The copy itself may have shifted sharing, validation, or references. See how to make a copy of a Google Sheets template without breaking it.

How to stop VLOOKUP breaking again#

Prevention is mostly structure:

Keep raw data, formulas, and dashboard outputs on separate tabs.
Keep dropdown source lists on one clearly named tab.
Standardize ID and code formats so text-vs-number mismatches can't creep in.
Protect formula cells, but make input cells obvious.

Build it so it's hard to break

A workbook that separates inputs, lookups, and reporting is far more resistant to lookup failures in the first place. The small-business setup guide shows that structure step by step.

The goal isn't a sheet that can never break. It's a sheet where the next break is obvious enough that a tired operator can fix it without opening ten tabs and hoping.

What to do next#

If the sheet is business-critical, make the smallest repair in a duplicate, test it with a realistic sample row, then apply it to the live version only once the symptom is gone. If the syntax itself is unclear, start with the plain-English Google Sheets formulas guide. If a different error is also showing up, see how to fix a formula parse error in Google Sheets.

Questions people ask

Why does VLOOKUP show #N/A when the value is clearly in the sheet?

Because the two values aren't truly identical. The usual culprits are a trailing space or a number stored as text — both look the same on screen but fail an exact match. Wrap each value in TRIM() in a spare cell to compare them as clean text, and confirm both are the same data type.

What's the difference between #N/A and #REF! in a lookup?

#N/A means the lookup ran fine but found no match — a data problem. #REF! means a reference no longer exists, usually because a row, column, or tab was deleted. They have different causes and different fixes.

Should I use TRUE or FALSE as VLOOKUP's last argument?

Use FALSE for business records like invoices, customer IDs, and product codes. FALSE forces an exact match. TRUE (or leaving it blank) does an approximate match that assumes the column is sorted and can quietly return the wrong value.

Is it safe to wrap VLOOKUP in IFERROR?

Yes, as a final step once the formula works — to show a clean message instead of #N/A. But add it last. While you're still debugging, IFERROR hides the real error you need to see, so strip it off first.