DATA CLEANUP & ERROR CHECKS
VLOOKUP Not Working? 7 Fixes (Numbers Stored as Text, Spaces and More)
VLOOKUP fails in two ways. The loud way is #N/A, even though you can see the value right there in the table. The quiet way is worse: it returns a believable but wrong number. This guide walks through the seven causes behind almost every broken VLOOKUP, shows the broken formula next to the fixed one, and ends with a checklist you can run on any lookup.
What you will build
The practice file has a small product list and seven problem cases, each with the broken formula and the fixed one side by side:
| Area | Cells | What it holds |
|---|---|---|
| Product list | A3:D11 | ID, Product, Category and Price for eight products, sorted by ID (inputs) |
| Problem cases | F3:J10 | Problem, lookup value (input), broken formula and fixed formula |
| Check | J12 | Confirms none of the fixed formulas returns an error |
| Symptom | Most likely fix |
|---|---|
#N/A, value is visibly there | Fix 2 (text vs number) or Fix 3 (spaces) |
| A wrong but believable number | Fix 1 (missing FALSE) or Fix 4 (column number) |
#N/A only on some rows after copying | Fix 6 (lock the range) |
| The formula itself shows in the cell | Fix 7 (Text format) |
#N/A, answer is left of the key | Fix 5 (INDEX/MATCH or XLOOKUP) |
Fix 1: Add FALSE for an exact match
VLOOKUP's fourth argument, range_lookup, is optional, and if you leave it out Excel uses approximate match. On an ID list that means: if the ID is not there, return the row with the next smaller ID. In I4:
=VLOOKUP(H4,$A$4:$D$11,4)ID 1009 does not exist, but the formula returns 29.75, the price of the Cable Tray (ID 1008). No error, just a wrong answer. Add FALSE (or 0) to force an exact match, and wrap it in IFNA so a missing ID says so. In J4:
=IFNA(VLOOKUP(H4,$A$4:$D$11,4,FALSE),"Not found")
Fix 2: Numbers stored as text
The IDs in column A are numbers. The lookup value in H5 came from a CSV export and is the text 1004. You can spot it because it is left-aligned and Excel shows a small green triangle. To VLOOKUP, the number 1004 and the text "1004" are different values, so =VLOOKUP(H5,$A$4:$D$11,4,FALSE) returns #N/A. Convert the lookup value inside the formula. In J5:
=VLOOKUP(--H5,$A$4:$D$11,4,FALSE)The double minus turns text that looks like a number into a number (VALUE(H5) does the same). If it is the other way round, with numbers in the cell and text in the table, use H5&"" to turn the lookup value into text. The lasting fix is to clean the column once: fix numbers stored as text shows four ways.
Fix 3: Extra spaces
H6 says Desk Lamp, but it was typed with a trailing space: "Desk Lamp ". To Excel that is a different value, so the broken formula returns #N/A. TRIM removes leading, trailing and repeated spaces. In J6:
=VLOOKUP(TRIM(H6),$B$4:$D$11,3,FALSE)Use =LEN(H6) to check: Desk Lamp has 9 characters, so a length of 10 gives the space away. If TRIM does not help, the culprit is usually a non-breaking space from a web page or PDF (CHAR(160)), which TRIM ignores: use SUBSTITUTE(H6,CHAR(160),""). Remove extra spaces and hidden characters and find nonprinting characters cover the rest.
Fix 4: The wrong column number
col_index_num counts columns from the left edge of the table range, not from column A of the sheet. In this table Price is column 4, so =VLOOKUP(H7,$A$4:$D$11,3,FALSE) returns Accessories from the Category column. Hard-coded numbers also break when someone inserts a column. Let MATCH find the column by its header. In J7:
=VLOOKUP(H7,$A$4:$D$11,MATCH("Price",$A$3:$D$3,0),FALSE)
Fix 5: The answer is to the left of the lookup column
VLOOKUP always searches the first column of the table and can only return columns to its right. To find the ID (column A) for a product name (column B), =VLOOKUP(H8,$A$4:$D$11,1,FALSE) searches column A for "Monitor Arm" and returns #N/A. Use INDEX/MATCH instead. In J8:
=INDEX($A$4:$A$11,MATCH(H8,$B$4:$B$11,0))MATCH finds the row of Monitor Arm in column B, and INDEX returns the value from that row of column A. In Microsoft 365, =XLOOKUP(H8,$B$4:$B$11,$A$4:$A$11) does the same in one step; see how to use XLOOKUP and INDEX MATCH vs XLOOKUP to pick one. For lookups across rows and columns at once, see the two-way lookup with INDEX MATCH.
Fixes 6 and 7: Lock the range, and un-format Text cells
Fix 6. If you write =VLOOKUP(H4,A4:D11,4,FALSE) in row 4 and copy it down, the table range moves with it. Five rows down it has become A9:D16, which no longer contains ID 1002, so I9 returns #N/A. Lock the range with dollar signs, $A$4:$D$11 (select the range in the formula and press F4), and every copy points to the same table.
Fix 7. I10 shows =VLOOKUP(H10,$A$4:$D$11,4,FALSE) as text instead of a result. The cell was formatted as Text before the formula was typed, so Excel stored it as a string. Set the format to General (Ctrl+1 > Number > General), then press F2 and Enter to re-enter the formula. If every formula in the sheet shows as text, Show Formulas is on: press Ctrl+` (the key left of 1) to switch it off.
More reasons a VLOOKUP returns the wrong result
| What you see | Likely cause | Fix |
|---|---|---|
| Correct formula, old result | Calculation is set to Manual | Formulas > Calculation Options > Automatic, or press F9. |
| New rows are never found | The table range stops above them | Turn the table into an Excel Table (Ctrl+T) and refer to it by name. |
| Returns the first of several matches | The key is duplicated | VLOOKUP returns the first match only; flag the duplicates first. |
| #N/A with dates | One side is a date, the other is text | Convert both to real dates with DATEVALUE. |
#REF! | col_index_num is larger than the number of columns | Count the columns in the table range, or use MATCH. |
#VALUE! | col_index_num is less than 1, or the lookup value is longer than 255 characters | Use a positive column number; use INDEX/MATCH for long text. |
In a bank reconciliation, where references come from two different systems, text numbers and spaces are the usual reason lines fail to match. Clean the keys first and most #N/A results disappear.
Frequently asked questions
Why does VLOOKUP return #N/A when the value exists?
Almost always because the two values are not truly identical: one is a number and the other text (Fix 2), or one has an extra or non-breaking space (Fix 3). Test with =H5=A7, which returns FALSE when they only look the same.
Should I use IFERROR or IFNA around VLOOKUP?
IFNA. It only catches #N/A (value not found) and lets real problems such as #REF! or #VALUE! show. IFERROR hides every error, including a broken formula.
Is VLOOKUP case-sensitive?
No. VLOOKUP treats DESK LAMP and Desk Lamp as the same. For a case-sensitive lookup, use INDEX/MATCH with EXACT inside an array formula, or XLOOKUP with EXACT.
Is TRUE or FALSE the default?
TRUE (approximate match) is the default when the fourth argument is left out. Always type FALSE or 0 for IDs, names and codes.
Should I switch to XLOOKUP?
If everyone who opens the file has Microsoft 365 or Excel 2021 and later, yes: XLOOKUP is exact-match by default, can look left and has a built-in if_not_found argument. See how to use XLOOKUP in Excel.
Final checklist
- The fourth argument is FALSE (or 0) for IDs, names and codes.
- The lookup value and the first column are the same type: both numbers or both text.
- No leading, trailing or non-breaking spaces (check with LEN).
- The column number points at the right column, or MATCH finds it by header.
- The answer is to the right of the lookup column, or INDEX/MATCH or XLOOKUP is used.
- The table range is locked with $ and covers every row.
- The formula cell is not formatted as Text, and Show Formulas is off.
Ready to move on from VLOOKUP? XLOOKUP exact match removes fixes 1, 4 and 5 entirely.
Stop fighting your lookups
Browse the OneXcel Tutorial Hub for more troubleshooting guides, explore ready-to-use Excel templates with clean lookups built in, or turn your data into a dashboard with the OneXcel Dashboard Builder.