VLOOKUP returns #N/A but the value is there: causes and fixes

When VLOOKUP returns #N/A for a value you can clearly see, the two values only look the same. Usually one of them is a number and the other is text, or one has a space at the end, or one lost its leading zeros. The rest are mistakes in the formula itself: an approximate match, or a table range that moved when you filled the formula down. XLOOKUP has the same data problems. It just avoids some of the formula mistakes.

Below: how to find which case you have in a few seconds, and the fix for each one.

1. Find the cause in a few seconds

Pick one row that returns #N/A and find the matching value in the lookup table by eye. Say the value you look for is in A2 and the one in the table is in B2 (copy it next to it if it is on another sheet). Then type three formulas:

• =A2=B2: TRUE means the values are equal and the formula is the problem (section 6). FALSE means the data is.
• =LEN(B2): the number of characters. If 1004 has 5, there is a hidden character.
• =LEN(TRIM(B2)): if this is smaller, the hidden characters are ordinary spaces. If it is the same, they are something TRIM does not remove.

Another quick tell: Excel aligns numbers to the right of a cell and text to the left, unless someone changed the alignment. A small green triangle in the corner of a cell means “number stored as text”.

2. Number on one side, text on the other

The value you type in A2 is the number 1003, but the table was pasted from an export and holds the text “1003”, or the other way round. Excel does not treat them as equal, so VLOOKUP and XLOOKUP return #N/A. Microsoft's article on #N/A errors covers this case with the first fix below.

Two ways to fix it:

• Convert the column. Select the column with text numbers, then Data → Text to Columns → Finish. Excel converts the text to numbers in place. This is the method in Microsoft's article.
• Convert in the formula. If the table holds text and you look up a number, turn the number into text: =VLOOKUP(A2&"", Supplier!A:C, 3, FALSE). If the table holds numbers and you look up text, turn the text into a number: =VLOOKUP(--A2, Supplier!A:C, 3, FALSE) (or VALUE(A2)).

The second way only works when the whole column has one type. If some rows of the table are numbers and some are text, which happens when typed and pasted values mix, convert the column.

3. Spaces you cannot see

“1004 ” with a space at the end is not “1004”. Spaces get in when data is typed by hand, exported from another system or copied from a web page.

• An ordinary space. TRIM removes spaces at the start and the end. Look up the trimmed value: =VLOOKUP(TRIM(A2), Supplier!A:C, 3, FALSE). If the spaces are in the table, clean the column: put =TRIM(A2) in a helper column, fill it down, then copy it and Paste Special → Values over the original.
• A non-breaking space. Web pages and some exports use character 160 instead of an ordinary space. Microsoft's documentation of TRIM says: “By itself, the TRIM function does not remove this nonbreaking space character.” Replace it first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
• Line breaks and other control characters. If LEN is still too long, CLEAN removes the first 32 non-printing characters (codes 0 to 31), such as a line break inside the cell: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).

4. Leading zeros

The ID is 00123 in one place and 123 in the other. When Excel opens a CSV file or you type a number, it drops the leading zeros, while systems that store IDs as text keep them. One side is text with zeros, the other a number without them.

First decide which one is right. For product codes, postcodes and account numbers the zeros are usually part of the ID. If all IDs have the same length, rebuild the zeros on the number side: =VLOOKUP(TEXT(A2, "00000"), Supplier!A:C, 3, FALSE) (five zeros for a five-character ID). If the zeros do not matter, compare both as numbers: =VLOOKUP(--A2, …) with the table converted as in section 2.

To stop Excel from dropping the zeros when you open a CSV, import it with Data → From Text/CSV, choose Transform Data and set the type of the ID column to Text, instead of opening the file directly.

5. Numbers calculated by a formula

The value you look up is the result of a formula, such as =F2-G2, and the cell shows 0.3. VLOOKUP still returns #N/A. If you type 0.3 by hand, it works.

The reason is that Excel stores numbers in binary, so a result like F2-G2 can be a tiny bit off, for example 0.30000000000000004 instead of 0.3. The cell rounds it for display, the lookup does not. Microsoft explains this in “Floating-point arithmetic may give inaccurate results in Excel”. The fix is to round the result to the decimals you need, in the formula that calculates it: =ROUND(F2-G2, 2). That is how the case in a January 2026 r/excel thread was solved.

6. When the formula is the problem

If =A2=B2 says TRUE and the lookup still fails, check the formula:

• The last argument is missing. Without it, VLOOKUP uses an approximate match, which expects the first column of the table to be sorted in ascending order. On unsorted data it returns #N/A or, worse, a value from the wrong row. Always end the formula with FALSE (or 0): =VLOOKUP(A2, Supplier!A:C, 3, FALSE). XLOOKUP matches exactly by default.
• The value is not in the first column of the range. VLOOKUP only searches the first column of the table you give it. Supplier!B:D will not find an ID in column A. Start the range at the ID column, or use XLOOKUP, which takes the lookup column and the return column separately: =XLOOKUP(A2, Supplier!A:A, Supplier!C:C). XLOOKUP is in Microsoft 365 and Excel 2021 or later, not in Excel 2016 or 2019.
• The range moved when you filled the formula down. A2:C100 in row 2 becomes A3:C101 in row 3, and the first rows of the table drop out of it. Lock it with dollar signs, $A$2:$C$100 (press F4 in the formula bar), or use whole columns, A:C.
• The value contains * or ?. With an exact match, VLOOKUP treats * and ? in a text value as wildcards, so it may return the wrong row. Microsoft's documentation says to put a tilde (~) in front of the character to find the character itself. XLOOKUP uses wildcards only when you ask for them (match mode 2).
• The value really is not there. Then #N/A is the right answer. Search the table with Ctrl+F for part of the value before you start fixing the data.

7. Show “Not found” instead of #N/A

Once the data is clean, the values that are left really are missing. To show your own text instead of the error, wrap the lookup in IFNA: =IFNA(VLOOKUP(A2, Supplier!A:C, 3, FALSE), "Not found"). XLOOKUP has this built in as the fourth argument: =XLOOKUP(A2, Supplier!A:A, Supplier!C:C, "Not found").

Prefer IFNA to IFERROR. IFERROR also hides other errors, such as a wrong column number, and then a broken formula looks like a missing value.

8. Comparing two lists or exports

Many lookups are not really lookups. They compare two versions of a list, such as your parts list and the supplier's new price list, to see what is new, what is gone and which prices changed. Then every case above shows up as a false “missing” row, and the real changes get lost among them.

The free compare tool on calamo.io matches the rows of two CSV files by an ID column and handles these cases by default:

• spaces at the start and end, including non-breaking spaces, are ignored;
• 2.40 and 2.4 are the same number;
• a CSV file has no number or text types, so 1003 is always 1003;
• leading zeros are kept: 00123 and 123 are two different IDs, because in an ID the zeros usually matter. You see one as removed and the other as added, so you can decide.

Here is the example from this guide. Our list has 1004, 1005 and 00123; the supplier's file has 1004 with a space, 1005 with a non-breaking space, and 123.

Save each sheet as CSV (File → Save As → CSV UTF-8) and drop both files into the tool. The files are not uploaded; the comparison runs in your browser. To try it with this example, download parts-ours.csv and parts-supplier.csv. For the formula way, see how to compare two lists in Excel and how to compare two columns in Excel.

Questions

Michal PijákData engineer and the maker of Calamo. These guides come from comparing exports at work: every formula and command is checked against the linked documentation. Found a mistake? hello@calamo.io