Before diving into fixes, a quick diagnostic: if your VLOOKUP returns #N/A, the lookup value was not found (reasons 1–3 below). If it returns #REF!, the column index is too large. If it returns #VALUE!, the lookup value is likely too long (reason 7). If it returns the wrong value with no error, check reasons 4–6.
1. Data Type Mismatch (Most Common)
The lookup value and the lookup column must be the same data type — both numbers or both text. If your lookup column contains numbers stored as text (left-aligned, green triangle in corner), and your lookup value is a real number, VLOOKUP will return #N/A every time even when the value visually appears in the column.
Fix: Select the lookup column → click the warning icon → "Convert to Number". Or use =VALUE() to convert: =VLOOKUP(VALUE(A2), B:C, 2, 0).
2. Extra Spaces in the Data
Invisible leading or trailing spaces in your lookup value or in the lookup column will cause VLOOKUP to fail silently. The value looks correct on screen, but it has a space before or after it that you cannot see.
Fix: Wrap the lookup value in TRIM(): =VLOOKUP(TRIM(A2), B:C, 2, 0). To clean the entire lookup column, use =TRIM() in a helper column, paste as values, then delete the original column.
3. Approximate Match When You Need Exact Match
The fourth argument of VLOOKUP controls match type: TRUE (or 1) means approximate match, FALSE (or 0) means exact match. If you omit the fourth argument or use TRUE, VLOOKUP will return the closest value less than or equal to the lookup value — which is rarely what you want for names, IDs, or codes.
Fix: Always use 0 (or FALSE) as the fourth argument for exact matches: =VLOOKUP(A2, B:C, 2, 0).
4. The Lookup Column Is Not the First Column of the Range
VLOOKUP always searches in the first column of the table_array range. If your lookup column is not the leftmost column of the range you specified, VLOOKUP will search the wrong column entirely.
Fix: Adjust your table_array range so the lookup column is first. Or switch to INDEX/MATCH which can look up any column regardless of position: =INDEX(C:C, MATCH(A2, B:B, 0)).
5. Column Index Number Is Wrong
The third argument (col_index_num) tells VLOOKUP which column to return, counted from the left of your table_array. If you insert or delete columns in the lookup table after writing the formula, this number goes out of sync.
Fix: Use MATCH to make the column reference dynamic: =VLOOKUP(A2, $B:$F, MATCH("Header", $B$1:$F$1, 0), 0). This automatically adjusts when columns are added or removed.
6. The Table Range Is Not Locked with Dollar Signs
When you copy a VLOOKUP formula down a column, the table_array range shifts down too unless you lock it with absolute references. A formula like =VLOOKUP(A2, B2:C100, 2, 0) becomes =VLOOKUP(A3, B3:C101, 2, 0) when copied down, missing the first row of data.
Fix: Always use absolute references for the table_array: =VLOOKUP(A2, $B$2:$C$100, 2, 0). Press F4 after selecting the range to add $ signs automatically.
7. Lookup Value Exceeds 255 Characters
VLOOKUP cannot match values longer than 255 characters. If your lookup value (typically a long text string) exceeds this limit, VLOOKUP will return #VALUE! or an unexpected result.
Fix: Switch to INDEX/MATCH which does not have this limitation: =INDEX(C:C, MATCH(A2, B:B, 0)).
When to Stop Using VLOOKUP
VLOOKUP has fundamental limitations: it only looks left-to-right, it breaks when columns are inserted, and it cannot handle values over 255 characters. For anything beyond simple lookups, use INDEX/MATCH instead:
=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))INDEX/MATCH works in any direction, is not affected by column insertions, handles long strings, and is generally faster on large datasets. Once you learn it, you will rarely use VLOOKUP again.
Recommended: Excel Formula Books
- →
- →
We may earn a small commission on purchases — at no extra cost to you. We only recommend resources we consider genuinely useful.
Automatically Find VLOOKUP Errors in Your Workbook
If you have a large workbook with many VLOOKUP formulas and are not sure which ones are returning errors, the ExcelErrorFinder audit tool will scan every cell and flag all #N/A, #VALUE!, and #REF! errors with their cell addresses and formula text — so you can fix them all at once rather than hunting cell by cell.