There are few things more frustrating in Excel than a VLOOKUP that refuses to work. One minute everything looks fine, the next you're staring at a column full of #N/A errors wondering what went wrong — and more importantly, how to fix it.
The good news is that broken VLOOKUPs almost always come down to one of a handful of very common mistakes. Once you know what to look for, most fixes take less than a minute. Here's a plain-English breakdown of the most common VLOOKUP problems and exactly how to solve them.
Before we dive into fixes, here's the basic structure of a VLOOKUP so we're on the same page:
In plain English: "Look for this value, in this range of data, and return whatever is in this column number. And tell me whether you want an exact match or approximate match." That last argument — exact vs. approximate — is where most people go wrong. More on that in a moment.
This is by far the most common VLOOKUP error. It means Excel looked through your data and couldn't find a match. Here are the three most likely reasons why:
Extra spaces hiding in your data. If your lookup value has a trailing space that the table data doesn't (or vice versa), Excel won't see them as a match. Fix it by wrapping your lookup value in TRIM:
You're using TRUE instead of FALSE. Always use FALSE as your last argument unless you specifically need an approximate match. Using TRUE causes Excel to find the closest match instead of an exact one, which almost always returns wrong results on business data.
Your data types don't match. If one column stores numbers as text and the other stores actual numbers, Excel won't match them. Check by clicking a cell and looking at the alignment — text aligns left, numbers align right.
This one is straightforward. If your table range is columns A to C (3 columns) but you asked for column number 4, Excel has nothing to return. Count your columns and make sure your col_index_num doesn't exceed the width of your table_array.
In this example the range is A to C — that's 3 columns. So valid column numbers are 1, 2, or 3. Asking for column 4 or higher will always return #REF.
This is the sneakiest VLOOKUP problem because Excel isn't telling you anything is wrong — it's just quietly returning bad data. The most common cause is that your lookup column is not the leftmost column in your table range.
VLOOKUP can only look to the right. It always searches the first column of your table_array and returns a value from a column to the right of it. If your lookup column is in the middle of your range, VLOOKUP is searching the wrong column entirely.
Here VLOOKUP searches column B for the value in A2, then returns whatever is in column C. Make sure the column you want to search is always the first column in your range.
This happens when your table_array uses specific row numbers instead of entire columns. If your range is A2:C50 and someone adds data in row 51, VLOOKUP won't find it because row 51 is outside your range.
The fix is simple — use entire columns instead of specific row ranges:
Using A:C instead of A2:C50 means your VLOOKUP will always search the entire column, no matter how many rows of data you add in the future.
If you've worked through all of these fixes and your VLOOKUP is still misbehaving, the issue is likely something specific to your data or spreadsheet structure that's harder to diagnose without seeing the actual file. Mismatched formatting, merged cells, hidden characters, and corrupted data can all cause VLOOKUP to behave unpredictably.
That last point is worth emphasizing — even a perfectly working VLOOKUP will occasionally return an error when a lookup value simply doesn't exist in your data. Wrapping it in IFERROR keeps your report looking clean and professional:
Instead of an ugly #N/A error, your spreadsheet will show "Not Found" — which is actually useful information rather than a confusing error code.
Send us your file and describe what's going wrong. We'll fix it fast, explain exactly what the problem was, and make sure it won't break again — starting at just $25.
📧 Get It Fixed Today