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.
The causes, in the order worth checking
- The value isn't in the list. Then #N/A is the right answer, and the question is what to show.
- It's in the list, but not identical: a space at the end, or a number on one side and text on the other.
- 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.
- An approximate match on a value below the first one in the list.
- #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:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Badge | Name | Dept | Hours | Badge | Dept | |
| 2 | E104 | Dana Brooks | Finance | 38 | E125 | #N/A | |
| 3 | E117 | Luis Ortega | Sales | 41 | |||
| 4 | E122 | Priya Nair | Support | 36 | |||
| 5 | E131 | Tom Becker | Sales | 40 | |||
| 6 | E140 | Grace Liu | Finance | 32 | |||
| 7 | E152 | Omar Haddad | Support | 44 |
F2 with "E122" to look up a badge that exists, and you get Support.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:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Badge | Name | Dept | Hours | Badge | Hours | |
| 2 | E104 | Dana Brooks | Finance | 38 | E131 | #N/A | |
| 3 | E117 | Luis Ortega | Sales | 41 | |||
| 4 | E122 | Priya Nair | Support | 36 | |||
| 5 | E131 | Tom Becker | Sales | 40 | |||
| 6 | E140 | Grace Liu | Finance | 32 | |||
| 7 | E152 | Omar Haddad | Support | 44 |
=LEN(F2) gives 5 for a four-character badge. TRIM removes the space: =VLOOKUP(TRIM(F2),A2:D7,4,FALSE) returns 40.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:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Badge | Name | Dept | Hours | Badge | Name | |
| 2 | E104 | Dana Brooks | Finance | 38 | E152 | #N/A | |
| 3 | E117 | Luis Ortega | Sales | 41 | |||
| 4 | E122 | Priya Nair | Support | 36 | |||
| 5 | E131 | Tom Becker | Sales | 40 | |||
| 6 | E140 | Grace Liu | Finance | 32 | |||
| 7 | E152 | Omar Haddad | Support | 44 |
=VLOOKUP(F2,A2:D7,2,FALSE) returns Omar Haddad.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:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Min lbs | Rate | Weight | Rate | |
| 2 | 1 | 6.5 | 0.5 | #N/A | |
| 3 | 5 | 9.25 | |||
| 4 | 10 | 14 | |||
| 5 | 25 | 22.5 | |||
| 6 | 50 | 38 |
D2 with 12 and you get 14, the rate for the 10 lb bracket. To cover small parcels, the table needs a first row that starts at 0.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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Badge | Name | Dept | Hours | Badge | Dept | |
| 2 | E104 | Dana Brooks | Finance | 38 | E125 | Not on staff | |
| 3 | E117 | Luis Ortega | Sales | 41 | |||
| 4 | E122 | Priya Nair | Support | 36 | |||
| 5 | E131 | Tom Becker | Sales | 40 | |||
| 6 | E140 | Grace Liu | Finance | 32 | |||
| 7 | E152 | Omar Haddad | Support | 44 |
"Not on staff" with "" (two quotes with nothing between them) for a cell that looks empty, or with 0 for a zero.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":
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Badge | Name | Dept | Hours | Badge | Hours | |
| 2 | E104 | Dana Brooks | Finance | 38 | E131 | Not found | |
| 3 | E117 | Luis Ortega | Sales | 41 | |||
| 4 | E122 | Priya Nair | Support | 36 | |||
| 5 | E131 | Tom Becker | Sales | 40 | |||
| 6 | E140 | Grace Liu | Finance | 32 | |||
| 7 | E152 | Omar Haddad | Support | 44 |
IFERROR to IFNA and the real problem shows. Then change the 5 to 4 and you get 40.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.
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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Badge | Name | Dept | Hours | Badge | Hours | |
| 2 | E104 | Dana Brooks | Finance | 38 | E125 | ||
| 3 | E117 | Luis Ortega | Sales | 41 | |||
| 4 | E122 | Priya Nair | Support | 36 | |||
| 5 | E131 | Tom Becker | Sales | 40 | |||
| 6 | E140 | Grace Liu | Finance | 32 | |||
| 7 | E152 | Omar Haddad | Support | 44 |
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.
=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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Badge | Name | Dept | Hours | Badge | Name | |
| 2 | E104 | Dana Brooks | Finance | 38 | E152 | ||
| 3 | E117 | Luis Ortega | Sales | 41 | |||
| 4 | E122 | Priya Nair | Support | 36 | |||
| 5 | E131 | Tom Becker | Sales | 40 | |||
| 6 | E140 | Grace Liu | Finance | 32 | |||
| 7 | E152 | Omar Haddad | Support | 44 |
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.
=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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Badge | Name | Dept | Hours | Badge | Hours | |
| 2 | E104 | Dana Brooks | Finance | 38 | E131 | ||
| 3 | E117 | Luis Ortega | Sales | 41 | |||
| 4 | E122 | Priya Nair | Support | 36 | |||
| 5 | E131 | Tom Becker | Sales | 40 | |||
| 6 | E140 | Grace Liu | Finance | 32 | |||
| 7 | E152 | Omar Haddad | Support | 44 |
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.