VLOOKUP that returns #N/A is the easy case. The hard one is VLOOKUP that returns a real-looking value — just the wrong one. Here are the six causes that produce silently wrong lookups, the diagnostic for each, and the deterministic check that surfaces them all.

Cause 1 — Duplicate keys in the lookup column (most common)

VLOOKUP returns the first match it finds. If your lookup column has accidental duplicates, lookups for that key return one specific row forever, regardless of which row you actually wanted.

Find them: in a helper column, run =COUNTIF(A:A, A2). Conditional-format any cell > 1 in red. For a one-shot check across the column:

=COUNTA(A:A) - COUNTA(UNIQUE(A:A))

Result is the count of duplicates. Zero is clean.

Fix: deduplicate the source data, or switch from VLOOKUP to QUERY / FILTER so all matching rows surface and the ambiguity is visible.

Cause 2 — Mixed data types in the key column

Numeric IDs imported from CSV often arrive partly as numbers and partly as text strings. 1234 and "1234" look identical on the screen but VLOOKUP does not match them.

Find them: on the lookup key column, run:

=COUNTA(A:A) - COUNT(A:A)

The result is the count of non-numeric cells. If you expect all numbers, anything > 0 is your bug.

Fix: coerce both sides to the same type. =VLOOKUP(VALUE(F2), A:B, 2, FALSE) converts the lookup key to a number. Or normalize the column with =VALUE(A2) in a helper column.

Cause 3 — `range_lookup` defaulted to `TRUE`

The fourth argument of VLOOKUP controls match behavior:

FALSE — exact match (what you almost always want).
TRUE or omitted — approximate match. Returns the largest value less than or equal to the lookup key, assuming the range is sorted ascending. If you forgot the fourth argument, you got this.

Find them: Find & Replace (Ctrl+H) with "search within formulas" enabled, search for VLOOKUP(. Inspect each formula's argument count.

Fix: always pass FALSE as the fourth argument. =VLOOKUP(A2, Products!A:C, 3, FALSE).

Cause 4 — Hidden whitespace or invisible characters

Data pasted from email, scraped from web, or imported from CSV often arrives with trailing spaces, tab characters, or non-breaking spaces. "CUST-001" and "CUST-001 " are different strings to VLOOKUP. Looks identical to your eye.

Find them:

=LEN(A2) <> LEN(TRIM(A2))

Returns TRUE for any cell with leading or trailing whitespace. Run as a helper column with conditional formatting.

Fix: wrap both sides in TRIM and CLEAN. =VLOOKUP(TRIM(F2), ARRAYFORMULA(TRIM(A:B)), 2, FALSE). Or normalize the source columns with a one-shot =ARRAYFORMULA(TRIM(CLEAN(A2:A))) in a helper column.

Cause 5 — Wrong column index (off by one)

You meant to return column C (the third column). You wrote 2. The formula succeeds and returns column B for every match. No error fires — just wrong data.

Find them: manually audit. =VLOOKUP(A2, X:Z, n, FALSE) — count the columns in the range and confirm n points at the right one. There is no formula-bar way to detect off-by-one.

Fix: rewrite to use INDEX/MATCH instead, which references the column by name or range:

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

Or upgrade to XLOOKUP, which takes the return range explicitly and is immune to off-by-one indexing.

Cause 6 — The data range silently shifted (schema drift)

Someone inserted a new column before "Price". Your =VLOOKUP(A2, Products!A:C, 3, FALSE) still computes a number, but column 3 in the new layout is "Tax Rate", not "Price". No error. Wrong number.

This is the worst class of bug because it cannot be detected by inspecting the formula alone — you have to compare the current column layout to what it was when the formula was written.

Find them: manual auditing requires keeping a locked reference sheet of the original layout. Most teams do not. The reliable approach is a tool that snapshots the schema each session and surfaces every formula whose target column moved or was renamed.

This is exactly what SheetBrain's Repair tab catches. When you use Generate or Debug, SheetBrain quietly snapshots the active sheet's column layout. When you click Repair after a column move or rename, it surfaces every formula whose reference target shifted — and proposes safe rewrites you can apply one-by-one or in batch.

Diagnostic checklist (paste this into a helper tab)

Run these against a suspect lookup column and see what lights up:

Duplicates: =COUNTA(A:A) - COUNTA(UNIQUE(A:A)) Mixed types: =COUNTA(A:A) - COUNT(A:A) Whitespace: =SUMPRODUCT(--(LEN(A2:A1000) <> LEN(TRIM(A2:A1000)))) Empty rows: =COUNTBLANK(A2:A1000)

Any non-zero result is a real defect that VLOOKUP will silently respect. Fix at the source-data level rather than wrapping every VLOOKUP in repair logic.

Scan your sheet for lookup defects

SheetBrain's Check tab (Detector Set v0.4) flags duplicate lookup keys, mixed-type columns that affect VLOOKUP type-matching, visible formula errors like #N/A, formula pattern outliers, missing copied formulas, and lookup formulas whose column index exceeds the referenced range — free, no AI quota, unlimited. Schema drift after a column move is handled separately by Repair on the $9/mo Pro plan.

Install free on Google Workspace

TL;DR

Duplicate keys — =COUNTIF(col, cell) in a helper column. Caught by SheetBrain's duplicate_lookup_key detector in Check.
Mixed types — =COUNTA(col) - COUNT(col). Caught by SheetBrain's mixed_text_number detector in Check.
Approximate match — pass FALSE as the fourth argument always. Manual fix; not a Check detector in v0.4.
Hidden whitespace — wrap with TRIM on both sides. Manual fix; not a Check detector in v0.4.
Wrong column index inside the range (off-by-one) — switch to INDEX/MATCH or XLOOKUP. Manual fix. SheetBrain's broken_reference detector flags the related case where the index exceeds the range.
Schema drift (column moved or renamed) — SheetBrain's Check tab does not detect this; the Repair tab does. Repair compares the active sheet to a captured schema snapshot and proposes safe rewrites.

VLOOKUP returning the wrong value? Six causes + fixes