Why your VLOOKUP isn’t working

Six causes account for nearly every broken VLOOKUP. They’re listed here in roughly the order they turn out to be the answer, and the first two are most of them.

Start by reading the error, because it narrows things immediately:

• #N/A — the value wasn’t found in the first column of the range. Causes 1 through 4.
• #REF! — the column index points past the end of the range. Cause 5.
• A number that’s simply wrong — no error at all. Causes 1 and 6, and the most dangerous of the set.

1. You left off the fourth argument

This is the most common cause, and the nastiest, because it usually doesn’t produce an error.

VLOOKUP’s fourth argument controls the match type, and leaving it out means approximate match, not exact:

=VLOOKUP(25, A2:B4, 2)          ← approximate. Almost never what you want.
=VLOOKUP(25, A2:B4, 2, FALSE)   ← exact. What you meant.

Here are both, over the same table. Change the lookup value and watch them disagree — at 20 they match, at 25 one of them invents an answer:

There is no 25 in that table. The first formula returns mid anyway — the row for 20, the closest value below. It looks entirely reasonable. There is no 25 in the data, and yet you get a value back, because approximate match returns the closest preceding row. On a price table or a rate card this produces numbers that are wrong by one tier, forever, silently.

Fix: always pass FALSE as the fourth argument. If you want the nearest-match behaviour deliberately, the data must be sorted ascending, or the result is meaningless.

2. The lookup value isn’t in the first column of the range

VLOOKUP only ever searches the leftmost column of the range you give it. Not the column you meant — the first one.

If your IDs are in column A and names in column B, VLOOKUP("Bo", A2:B4, 2, FALSE) returns #N/A, because it is searching the IDs for “Bo” and never looks at column B at all.

Fix: start the range at the column being searched — VLOOKUP("Bo", B2:C4, …). If the value you want to return sits to the left of the one you’re searching, VLOOKUP cannot do it at all; use XLOOKUP or INDEX/MATCH.

3. Stray whitespace

A trailing space is invisible on screen and fatal to an exact match. "Bo " and "Bo" are different strings, and both return #N/A against the other:

This is extremely common in data exported from other systems, where a fixed-width field leaves padding behind.

Fix: wrap the lookup value in TRIM(), or clean the source column once with TRIM and use the cleaned version. Cleaning the data beats wrapping every formula that touches it.

4. Numbers stored as text

In most spreadsheets this is a top-three cause: a lookup value of "102" (text) will not match a cell containing 102 (number), and both render identically.

A lookup value of "102" (text) will not match a cell containing 102 (number), and both render identically. This is the top-three cause everywhere, and VisiGrid behaves the same as Excel and Google Sheets: types must agree. Text and a number are never equal, however alike they look.

Fix it by making both sides the same type. Either direction works:

=VLOOKUP(VALUE(A1), B:C, 2, FALSE)     turn the text key into a number
=VLOOKUP(A1, B:C, 2, FALSE)            with the column converted to text

VALUE() converts text to a number; TEXT(A1,"@") converts a number to text. Pick whichever matches the column you are searching, and convert the source once rather than wrapping every formula that touches it.

Marking a cell as text

A leading apostrophe does it, as in every other spreadsheet:

'102        the text 102 — three characters, and the apostrophe is not part of it
102         the number 102

That is what lets a column of ZIP codes, account numbers or SKUs keep its leading zeros and still behave predictably in a lookup.

COUNTIF and SUMIF are different on purpose

Their second argument is not a lookup value — it is a criterion, a small pattern language that also understands ">5", "<>x" and "a*". Numeric-looking criteria are compared numerically, so =COUNTIF(A:A,"102") counts cells holding the number 102. That is deliberate and matches how criteria work in Excel; it is not the lookup rule leaking.

5. #REF! — the column index points past the range

#REF! is a different failure from #N/A, and the distinction saves time.

=VLOOKUP(102, A2:B4, 5, FALSE)

That range is two columns wide. There is no fifth column, so the formula cannot return anything:

error: formula returned #REF!
hint:  a cell reference is out of range; check your formula references

Fix: count the columns in the range and make sure the third argument is within it. Column 1 is the one being searched, so a two-column range only ever supports an index of 1 or 2.

6. The range moved when you copied the formula

A formula that works in the first row and fails further down is almost always this. Range references are relative by default, so A2:D100 becomes A3:D101 when copied one row down, and the bottom of your lookup table slides out of view one row at a time.

Fix: lock the range with dollar signs — $A$2:$D$100 — before copying. This is what absolute references are for, and it is the single most useful habit for anyone writing lookups by hand.

A note on wildcards

VLOOKUP supports wildcards in an exact-match lookup: * for any number of characters, ? for exactly one, and ~ to escape either. So "be*" matches beta.

They do nothing in an approximate match, which is another reason to always pass FALSE.

Reading the error instead of guessing

Every result on this page was produced by running the formula, not transcribed from documentation. That is easier here than in most spreadsheets, because VisiGrid says what went wrong rather than only that something did:

error: formula returned #N/A
hint:  lookup function did not find a match

error: formula returned #REF!
hint:  a cell reference is out of range; check your formula references

A bare #N/A tells you a lookup failed. It doesn’t tell you whether the value was missing, the range was wrong, or the type didn’t match — which is why diagnosing one usually means rebuilding the formula piece by piece until it works again.

Naming the failure is not a substitute for the six causes above. It just means you start at the right one.