Excel practice › Excel errors › #N/A error

#N/A error in Excel: what it means and how to fix it

#N/A means "not available": a formula looked for a value and didn't find it. It comes from the lookup functions, VLOOKUP, XLOOKUP, HLOOKUP and MATCH (so INDEX/MATCH too). Either the value isn't where the formula searched, or it's there in a form that doesn't match.

If a missing value is normal in your sheet, say what to show in its place: =IFNA(VLOOKUP(F2,A2:D7,3,FALSE),"Not on staff"). If the value should be there, don't hide the error. Find out why it doesn't match.

Skip to the 3 exercises ↓

The causes, in the order worth checking

  1. The value isn't in the list. Then #N/A is the right answer, and the question is what to show.
  2. It's in the list, but not identical: a space at the end, or a number on one side and text on the other.
  3. The formula doesn't search where the value is: the range stops too early, or a VLOOKUP table doesn't start at the right column.
  4. An approximate match on a value below the first one in the list.
  5. #N/A was typed on purpose as a placeholder for missing data, with =NA(). Formulas that use that cell show #N/A too, until the real value is entered.

1. The value isn't in the list

A staff list with six badges, and a lookup for a seventh:

Live example#N/A: there is no badge E125
G2fx
ABCDEFG
1BadgeNameDeptHoursBadgeDept
2E104Dana BrooksFinance38E125#N/A
3E117Luis OrtegaSales41
4E122Priya NairSupport36
5E131Tom BeckerSales40
6E140Grace LiuFinance32
7E152Omar HaddadSupport44

2. It's there, but not identical

Lookups compare whole values. A badge with a space after it is a different text, and Excel treats the number 104 and the text "104" as different values. Both look fine on screen. The space is the one you can test here:

Live example#N/A: E131 with a space after it
G2fx
ABCDEFG
1BadgeNameDeptHoursBadgeHours
2E104Dana BrooksFinance38E131 #N/A
3E117Luis OrtegaSales41
4E122Priya NairSupport36
5E131Tom BeckerSales40
6E140Grace LiuFinance32
7E152Omar HaddadSupport44

For numbers stored as text, and the other causes that are specific to VLOOKUP, see VLOOKUP not working.

3. The formula doesn't search where the value is

A range written when the list had four people doesn't grow when two more are added below it. The formula keeps working for the old rows, and returns #N/A for the new ones:

Live example#N/A: the range stops at row 5
G2fx
ABCDEFG
1BadgeNameDeptHoursBadgeName
2E104Dana BrooksFinance38E152#N/A
3E117Luis OrtegaSales41
4E122Priya NairSupport36
5E131Tom BeckerSales40
6E140Grace LiuFinance32
7E152Omar HaddadSupport44

The same thing happens sideways: VLOOKUP only searches the first column of its range, so a range that starts one column too early or too late never finds the value.

4. Approximate match below the first value

VLOOKUP without FALSE, and MATCH without 0, look for the largest value that is less than or equal to yours. That's the right tool for brackets, like a shipping rate by weight. It returns #N/A when your value is smaller than the first one in the list:

Live example#N/A: 0.5 lbs is below the first bracket
E2fx
ABCDE
1Min lbsRateWeightRate
216.50.5#N/A
359.25
41014
52522.5
65038

For IDs, names and codes, an approximate match isn't what you want: write FALSE in VLOOKUP and 0 in MATCH.

Turn #N/A into a blank, a 0 or a message

IFNA(value, value_if_na) returns the first argument, unless it is #N/A: then it returns the second.

Live exampleA message where #N/A would be
G2fx
ABCDEFG
1BadgeNameDeptHoursBadgeDept
2E104Dana BrooksFinance38E125Not on staff
3E117Luis OrtegaSales41
4E122Priya NairSupport36
5E131Tom BeckerSales40
6E140Grace LiuFinance32
7E152Omar HaddadSupport44

A blank made with "" is an empty text, not a number. If other formulas will add or multiply the result, return 0. XLOOKUP has this built in as its fourth argument: =XLOOKUP(F2,A2:A7,C2:C7,"Not on staff").

Why hiding it everywhere is a bad idea

An #N/A is information: this badge isn't in the list, which often means a typo in the badge. Wrap every lookup in a blanket handler and that signal is gone. IFERROR is the risky one, because it replaces every error, not only #N/A. Below, the badge exists and the formula still says "Not found":

Live exampleIFERROR hiding a different mistake
G2fx
ABCDEFG
1BadgeNameDeptHoursBadgeHours
2E104Dana BrooksFinance38E131Not found
3E117Luis OrtegaSales41
4E122Priya NairSupport36
5E131Tom BeckerSales40
6E140Grace LiuFinance32
7E152Omar HaddadSupport44

Practice: 3 formulas to fix

Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data.

Exercise 1#N/A to 0

Badge E125 isn't on the list, so =VLOOKUP(F2,A2:D7,4,FALSE) shows #N/A, and the payroll total that adds this cell shows #N/A too. In G2, return the hours for the badge in F2, or 0 when the badge isn't found.

G2fx
ABCDEFG
1BadgeNameDeptHoursBadgeHours
2E104Dana BrooksFinance38E125
3E117Luis OrtegaSales41
4E122Priya NairSupport36
5E131Tom BeckerSales40
6E140Grace LiuFinance32
7E152Omar HaddadSupport44
Show the answer

=IFNA(VLOOKUP(F2,A2:D7,4,FALSE),0) gives 0.

IFNA passes the hours through when the badge is found and returns 0 when the lookup ends in #N/A. A 0 can be added up; an #N/A spreads to every formula that uses the cell.

Exercise 2The range stops too early

=VLOOKUP(F2,A2:D5,2,FALSE) returns #N/A for badge E152, who is on the list. In G2, write the formula that finds the name for the badge in F2.

G2fx
ABCDEFG
1BadgeNameDeptHoursBadgeName
2E104Dana BrooksFinance38E152
3E117Luis OrtegaSales41
4E122Priya NairSupport36
5E131Tom BeckerSales40
6E140Grace LiuFinance32
7E152Omar HaddadSupport44
Show the answer

=VLOOKUP(F2,A2:D7,2,FALSE) gives Omar Haddad.

The range has to cover every row of the list. A2:D7 includes rows 6 and 7, where the two newest badges are.

Exercise 3IFERROR is hiding the real error

=IFERROR(VLOOKUP(F2,A2:D7,5,FALSE),"Not found") says Not found for badge E131, who is on the list. In G2, write a formula that returns the hours for the badge in F2, shows Not found only when the badge is missing, and doesn't hide other errors.

G2fx
ABCDEFG
1BadgeNameDeptHoursBadgeHours
2E104Dana BrooksFinance38E131
3E117Luis OrtegaSales41
4E122Priya NairSupport36
5E131Tom BeckerSales40
6E140Grace LiuFinance32
7E152Omar HaddadSupport44
Show the answer

=IFNA(VLOOKUP(F2,A2:D7,4,FALSE),"Not found") gives 40.

Hours is the 4th column of A2:D7. IFNA replaces #N/A only, so the next wrong column number or misspelled function will show as an error and not as "Not found".

Questions people ask

What does #N/A mean in Excel?

"Not available." A formula was asked to find a value and couldn't. It usually comes from a lookup: VLOOKUP, XLOOKUP, HLOOKUP or MATCH. It doesn't mean the formula is written wrong. It means the value wasn't found where the formula looked.

How do I change #N/A to blank in Excel?

Wrap the lookup in IFNA and return an empty text: =IFNA(VLOOKUP(F2,A2:D7,3,FALSE),""). The cell looks empty when the value isn't found, and shows the result when it is.

How do I change #N/A to 0 in Excel?

The same way, with a zero: =IFNA(VLOOKUP(F2,A2:D7,4,FALSE),0). Use it when the result goes into a total and a missing value should count as nothing.

Should I use IFNA or IFERROR?

IFNA when the only thing you expect is a missing value. IFERROR also replaces #REF!, #VALUE!, #DIV/0!, #NAME? and the rest, so it hides a wrong column number or a misspelled function as well. With IFNA those stay visible.

Why do I get #N/A when the value is in the list?

The two values aren't identical (a space at the end, or a number against text), or the formula's range doesn't include the row or column where the value is. VLOOKUP not working goes through each case.

Keep going

Lesson 3 is six VLOOKUP drills on a coffee-shop menu, including a missing item handled with IFERROR and a table locked with $ before copying down. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

VLOOKUP not working: causes and fixesIFERROR and IFNA, with live examplesVLOOKUP function, with live examplesXLOOKUP function, with live examplesINDEX MATCH, with live examples#REF! error in Excel: meaning and fixesExcel formula errors, fixed liveExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: How to correct a #N/A error, Microsoft Support: IFNA function, Microsoft Support: IFERROR function, Microsoft Support: VLOOKUP function, Microsoft Support: XLOOKUP function. Last checked November 2026.