How to Fix #N/A Errors in VLOOKUP and XLOOKUP

An Excel N/A error, shown as #N/A, means a lookup searched for your value and found no match. Sometimes the item is truly missing. More often, it is there but doesn't match exactly, because of a hidden space, a number stored as text or a table range that moved. This guide shows how to find the cause, fix it, and only then hide the errors that are left.

What does the Excel N/A error mean?#

#N/A stands for "not available": the function has no value to give you. In VLOOKUP, XLOOKUP, HLOOKUP and MATCH, it almost always means "I couldn't find what you asked for".

That makes #N/A different from other lookup errors:

The last row is the most dangerous, because nothing warns you. If you are new to lookups, start with our beginner's guide to VLOOKUP or the VLOOKUP function guide.

Step 1: check whether the value is really missing#

Before you change the formula, look for the value yourself. Copy the lookup value and press Ctrl+F (Mac: ⌘F) to search the table for it. Two things can happen:

• It isn't there. The code might be a typo, like K-301 for K-310, or the item is new or discontinued. In that case #N/A is the honest answer. Fix the typo, or label the missing item (see Step 5).
• It looks like it is there. Then the two values only look the same. Go to Step 2.

On a big sheet, first find every error at once. Select the column with your lookups and choose Home > Find & Select > Go To Special. Pick Formulas, leave only Errors ticked, and click OK. Excel selects every cell with an error, so you can see whether one row is broken or all of them. If every row shows #N/A, check the formula first (Step 4). If only a few do, check the data (Steps 2 and 3).

Step 2: remove hidden spaces with TRIM#

Extra spaces are the most common reason a lookup misses. Data copied from other systems often has a space at the end, like "P-205 ". You can't see it, but "P-205 " and "P-205" are different text.

Check with LEN, which counts characters. P-205 has 5 characters, so =LEN(B3) returning 6 means there is a hidden space. Then wrap the lookup value in TRIM, which removes spaces from the start and end of text:

=VLOOKUP(TRIM(B3),$F$2:$H$8,2,FALSE)

TRIM goes around the value you look up, not around the whole VLOOKUP. If the spaces are in the table instead, clean that column once with TRIM in a helper column, then paste the results as values.

Step 3: fix numbers stored as text#

A number and the same digits stored as text don't match. Employee ID 1042 in one sheet and "1042" in another (often shown with a small green triangle in the cell's corner) give #N/A.

• =ISNUMBER(A2) returns TRUE for a real number and FALSE for text.
• If your lookup value is text but the table has numbers, use VALUE(A2) as the lookup value.
• If your lookup value is a number but the table has text, use A2&"" to turn it into text.

=VLOOKUP(VALUE(A2),$F$2:$H$8,2,FALSE)

A lasting fix is to convert the column itself: select it and use the warning icon's Convert to Number option.

Step 4: check the formula for other causes#

If the value is there and clean, check the formula itself:

• The table isn't locked. =VLOOKUP(B2,F2:H8,2,FALSE) becomes F3:H9, then F4:H10 as you fill down, and the top rows of the table fall out of range. Lock it: $F$2:$H$8.
• VLOOKUP is searching the wrong column. VLOOKUP always searches the first column of the table. If the value you know is to the right of the one you want, VLOOKUP can't find it. Use INDEX MATCH or XLOOKUP to look up to the left.
• The table doesn't include new rows. If the price list grew from row 8 to row 12, a table of $F$2:$H$8 can't find the new items. Extend the range, or turn the list into an Excel table (Ctrl+T, Mac: ⌘T) so it grows by itself.
• An approximate match below the first band. With TRUE, a number smaller than the first value in the table gives #N/A. Start the tier table at 0.
• Wildcards in XLOOKUP. XLOOKUP treats * as a normal character unless match_mode is 2. See XLOOKUP wildcards and match modes.

Step 5: label missing items with IFNA or XLOOKUP#

Once every real problem is fixed, the #N/A errors left are items that truly aren't in the table. Show a clear message instead. IFNA replaces only #N/A:

=IFNA(VLOOKUP(TRIM(B2),$F$2:$H$8,2,FALSE),"Discontinued")

XLOOKUP (Excel 2021 or Microsoft 365) has the message built in, as its 4th argument:

=XLOOKUP(TRIM(B2),$F$2:$F$8,$H$2:$H$8,"Check code")

For a number you will add up, use 0 instead of text, so totals still work. With the quantity in C2, this gives 0 for a missing item instead of an error: =C2*IFNA(VLOOKUP(B2,$F$2:$H$8,3,FALSE),0).

IFERROR or IFNA: which should you use?#

Use IFNA for lookups. IFERROR catches every error, including #REF! from a wrong column number and #NAME? from a misspelled function. Those are your mistakes, and you want to see them. IFNA catches only "not found".

IFERROR is still useful for other errors, like #DIV/0! when you divide by zero. Read more in IFERROR in Excel and the IFERROR function guide, and see the full comparison in IFERROR vs IFNA. Whichever you use, check the formula works before you wrap it: a wrapper hides the problem, it doesn't fix it.

Practise it#

The best way to get fast at this is to fix broken lookups yourself. ExcelReady's fixing lookup errors lesson gives you a sheet with a #REF!, a silent wrong price, a hidden space and a discontinued item to diagnose and fix, ending with IFNA and XLOOKUP. The IFERROR lesson covers #DIV/0! and shows how a wrapper can hide your own typo. Try the TRIM exercise on this page first.