VLOOKUP not working: 7 causes and fixes

Cause 1: the last argument is missing

=VLOOKUP(E5,A2:C7,3) has no fourth argument, so Excel uses approximate match. Approximate match assumes the first column is sorted from smallest to largest. When it is not, VLOOKUP can quietly return the amount from a different order with no error at all, which is worse than #N/A because nobody notices.

Always end an ID or name lookup with FALSE (or 0). Then VLOOKUP only returns a row whose ID matches exactly, and gives #N/A when there is none.

Cause 2: a number looking for text

Order 10482 in A2 was imported as text (it sits on the left of the cell and may show a small green triangle). E2 holds the real number 10482. To Excel these are different values, so the exact lookup fails with #N/A even though both cells look identical.

Check with =ISTEXT(A2) and =ISTEXT(E2). If one is TRUE and the other FALSE, you have found the problem. The quick fix inside the formula is to turn the number into text by joining it with an empty string.

Cause 3: text looking for a number

The opposite case: E3 holds "10475" as text (often because the column was formatted as Text, or the value came from a form), while the table holds the number 10475.

Put two minus signs in front of the lookup value. The double negative converts text that looks like a number into a real number. The permanent fix is to convert the whole column: select it, click the warning icon, and choose Convert to Number.

Cause 4: a hidden space

A4 contains "10491 " with a trailing space, a classic leftover from copying out of another system. =LEN(A4) returns 6 for a 5-character ID, which gives it away.

Clean the source column rather than patching every formula: add a helper column with =TRIM(A2), fill it down, copy it, and paste as values over the original. Non-breaking spaces from web pages survive TRIM; replace them with =SUBSTITUTE(A2,CHAR(160),"") or use Find and Replace.

Causes 5 to 7: column number, lookup column, unlocked range

Column number: the third argument counts columns inside the table you selected, not from column A of the sheet. In A2:C7, Customer is 2 and Amount is 3. If you insert a column into the table later, a hard-coded 3 now points at the wrong column.

Lookup column: VLOOKUP can only search the first column of the table and return something to its right. If the ID sits to the right of the value you want, use XLOOKUP or INDEX with MATCH.

Unlocked range: if you copy =VLOOKUP(E2,A2:C7,3,FALSE) down, the table slides to A3:C8, A4:C9 and so on, and rows near the top drop out. Lock it with dollar signs: $A$2:$C$7.

Only hide #N/A once the lookup is correct

IFERROR is useful for IDs that genuinely might not exist, but wrapping a broken lookup in it just hides the bug. Fix the causes above first, then add a friendly message for real misses.