Excel #N/A error
Excel could not find the value you are looking for. It is the classic VLOOKUP, XLOOKUP and MATCH error.
Why it appears
- The value really is not in the list.
- There are extra spaces, or a number is stored as text ("100" is not 100).
- The lookup range does not include the row with the value.
- VLOOKUP only searches the first column of the range.
How to fix it
- Clean the data with TRIM() or convert it with VALUE().
- Widen the range and anchor it with $ before copying the formula.
- If a missing value is expected, show other text with IFNA or XLOOKUP’s fourth argument.
Gives the error
=VLOOKUP(E2,A2:B6,2,FALSE)
Fixed
=IFNA(VLOOKUP(E2,$A$2:$B$6,2,FALSE),"No existe")
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Is it on the list? ISNA
MATCH returns #N/A when it cannot find a value. ISNA detects exactly that error, so IF(ISNA(MATCH(…)), …) tells you whether something is missing from another list. ISERROR catches any error.
=IF(ISNA(MATCH(A2,$D$2:$D$5,0)),"Falta","Registrado")ESNOD = ISNAESERROR = ISERRORCOINCIDIR = MATCHCOINCIDIRX = XMATCHSI = IFIn B2, show "Missing" if the guest is not on the registered list and "Registered" if they are.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Guest | Status | Registered | ||
| 2 | Ana | Sofía | |||
| 3 | Luis | Ana | |||
| 4 | Sofía | Pablo | |||
| 5 | Diego | Jorge | |||
| 6 | Marta | ||||
| 7 | Pablo | ||||
| 8 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What does #N/A mean in Excel?
- Excel could not find the value you are looking for. It is the classic VLOOKUP, XLOOKUP and MATCH error.
- How do I get rid of the #N/A error?
- Clean the data with TRIM() or convert it with VALUE(). Widen the range and anchor it with $ before copying the formula. If a missing value is expected, show other text with IFNA or XLOOKUP’s fourth argument.
- What is #N/A called in Spanish Excel?
- Spanish Excel shows it as #N/D.