VLOOKUP Not Working? Fix #N/A When the Value Is There
VLOOKUP returns #N/A or the wrong value? Test for text numbers, hidden spaces, approximate match and unlocked ranges, then fix each one or switch to XLOOKUP.
- Excel
- Data Quality
- Reporting Workflows

On this page
- Why is my VLOOKUP not working?
- Why does VLOOKUP return #N/A when the value is there?
- Why does VLOOKUP return the wrong value?
- Why does it work in the first row and fail further down?
- What if the ID is not in the first column?
- How do you show a blank instead of #N/A?
- Should you switch to XLOOKUP?
- Where does Glow Reports fit?
- Common questions
You type a VLOOKUP, the ID is right there in the other table, and Excel answers #N/A. Or it returns a value, but the wrong one. Almost every case comes down to five causes. An approximate match is left on, numbers are stored as text on one side, hidden spaces sit in the key, the ID is not in the first column, or the range moved when you filled the formula down. Each one has a quick test and a fix, and the same tests work in Google Sheets.
The examples below look up a customer's plan. Your order export sits on one sheet with customer IDs in column A. The customer list sits on a sheet called , with the ID in column A, the name in B and the plan in C.
=VLOOKUP(A2,Customers!$A$2:$C$500,3,FALSE)Why is my VLOOKUP not working?
Because the value you look up and the value in the table are not identical, or because the formula searches the wrong place. Find your symptom in the table below, then jump to the section that fixes it.
| Symptom | Likely cause | Fix |
|---|---|---|
| #N/A for a value you can see in the table | Number stored as text on one side, or extra spaces | Convert the type, or wrap the key in TRIM |
| A value comes back, but it's the wrong one | Last argument left out, so the match is approximate | Add FALSE as the last argument |
| The first rows work, rows further down fail | The table range moved as you filled down | Lock the range with dollar signs |
| #N/A on every row | The ID is not in the first column of the range | Move the range, or use XLOOKUP |
| #REF! | Column number larger than the range | Count the columns again |
| #N/A for values that are missing | The value is not in the table | Show a blank or a label with IFNA |
Why does VLOOKUP return #N/A when the value is there?
The two cells look the same but hold different values. In most exports, one file stores the ID as a number and the other as text, or one side carries a space you can't see. Excel compares the stored value, not what is displayed.
Test it before you change anything. In an empty cell, type , then . If one says TRUE and the other FALSE, the types differ. Microsoft lists this as the first cause of #N/A: the lookup value and the source data are different data types Microsoft Support.
Two ways to fix a type mismatch:
- Convert the column for good. Select it, set the format you want with Ctrl+1, then run DataText to ColumnsFinish so Excel reapplies the type to each cell.
- Convert inside the formula. If the table holds numbers and your key is text, look up . If the table holds text and your key is a number, look up .
=VLOOKUP(VALUE(A2),Customers!$A$2:$C$500,3,FALSE)Spaces are the other usual suspect. Compare with the length you expect. A count one higher than the visible characters means a trailing space. TRIM removes every space except single spaces between words. It leaves the nonbreaking space, character 160, that web pages use Microsoft Support. Replace that one first: .
If the table side is dirty, clean that column once instead of fixing every formula. The full routine for spaces, text numbers and dates is in how to clean data in Excel.
Why does VLOOKUP return the wrong value?
The last argument is missing or set to TRUE, so VLOOKUP looks for an approximate match. It returns the nearest value below your key instead of saying it found nothing. Set the last argument to FALSE for every ID, email or product code lookup.
Approximate match is the default when you leave the argument out, and it expects the first column sorted in ascending order Microsoft Support. On an unsorted export, it returns plausible but wrong plans, which is worse than an error because nobody notices. Approximate match has one good use: looking up a value in a band, such as a commission rate for a sales amount, from a table sorted by the lower bound of each band.
Why does it work in the first row and fail further down?
The table range is not locked. When you fill down one row, it becomes , then , and the top of the customer list drops out of the search. Customers near the top return #N/A in the lower rows. Exceljet lists an unlocked table reference among the usual causes of #N/A in a copied VLOOKUP Exceljet.
Add dollar signs so the range stays put: . Select the range inside the formula and press F4 to add them Microsoft Support. The lookup value, , stays relative so it moves with each row. A formatted table, made with Ctrl+T, avoids the problem too, since its references name the column instead of the cells.
What if the ID is not in the first column?
VLOOKUP only searches the first column of the range and returns a value to its right. If the ID sits in column B and the range starts at A, every row returns #N/A. Start the range at the ID column, or switch to XLOOKUP, which searches any column.
The column number counts from the left edge of the range, not from column A of the sheet Microsoft Support. With , the plan in column D is column 3. A number larger than the range returns #REF!. Someone inserting a column in the customer sheet shifts the plan to column 4 while your formula still asks for 3, and the result changes without any error. XLOOKUP points at the return column directly, so an inserted column does not break it.
How do you show a blank instead of #N/A?
Wrap the lookup in IFNA once you have checked that the missing values are missing for real. IFNA replaces only the #N/A error, so a #REF! or #VALUE! from a broken formula still shows.
=IFNA(VLOOKUP(A2,Customers!$A$2:$C$500,3,FALSE),"Unknown")IFNA returns the value you give when the formula returns #N/A Microsoft Support. IFERROR also works, but it hides every error, including a range you typed wrong. Count the labels before you send the report. If many rows say Unknown, go back to the type and space tests above. To list which IDs are missing from the other table, use the methods in how to compare two columns in Excel.
Should you switch to XLOOKUP?
Yes, if your Excel has it. XLOOKUP defaults to an exact match, searches any column, returns a column you point at, and takes a fourth argument for the not-found case. It is in Excel for Microsoft 365 and Excel 2021 and later, not in 2016 or 2019.
=XLOOKUP(A2,Customers!$A$2:$A$500,Customers!$C$2:$C$500,"Unknown")Microsoft documents exact match as the default and the optional text returned when no match is found Microsoft Support. XLOOKUP still fails on a text number or a trailing space, so the tests above apply to both functions. Google Sheets has VLOOKUP with the same arguments, where the last one also defaults to an approximate match Google Docs Editors Help, and XLOOKUP too Google Docs Editors Help.
Where does Glow Reports fit?
The fixes above work in any copy of Excel or Google Sheets, and a lookup you run once a month is fine there. Glow Reports helps when the lookup is the first step of a report.
Take revenue by plan from an order export and a customer list. You upload both files, CSV or Excel, and ask for the figure in plain language.
The AI writes the SQL that joins the two files, builds the charts and drafts the text. The SQL sits under each figure, so you can read which columns it matched on and edit the query if it picked the wrong one. A CSV upload detects its separator, comma, semicolon or tab. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.
Common questions
Why is my VLOOKUP returning #N/A when the value exists?
The value in your cell and the value in the table are stored differently, most often a number against text, or a trailing space. Test both cells with ISNUMBER and LEN, then convert the type or wrap the key in TRIM.
What does FALSE mean at the end of VLOOKUP?
FALSE asks for an exact match. Without it, VLOOKUP uses an approximate match and returns the nearest smaller value from a sorted first column, which gives wrong results on an unsorted list.
Can VLOOKUP look to the left?
No. VLOOKUP returns a value from a column to the right of the one it searches. Use XLOOKUP, or INDEX with MATCH in older versions, to return a value from a column on the left.
Why does my VLOOKUP work on one sheet and not when I copy it?
The table range is relative, so it moves when you copy or fill the formula. Lock it with dollar signs, , or point the formula at a formatted table.
I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.
More from El Mehdi BenaneUpload your order export and customer list to Glow Reports and ask for the figure, with the SQL join under each chart.


