#N/A means "not available": a lookup such as VLOOKUP, XLOOKUP or MATCH did not find the value it was looking for. Below, =VLOOKUP(E2,A2:B6,2,FALSE) returns #N/A because Kiwi is not in the list. Change E2 to Pear and it returns 1.5.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Look for | Price | ||
| 2 | Apple | 1.2 | Kiwi | #N/A | ||
| 3 | Pear | 1.5 | ||||
| 4 | Plum | 0.8 | ||||
| 5 | Bread | 2.4 | ||||
| 6 | Milk | 1.1 |
#N/A The value you looked for is not in the lookup range.When the value really is missing, #N/A is the right answer, and IFNA (further down) turns it into a message. The cases worth fixing are the ones where the value is there and the lookup still fails. Excel in some languages shows the error under its own name, such as #NV in German, #N/D in Portuguese and Italian, #Н/Д in Russian and #YOK in Turkish; it is the same error.
#N/A after filling a lookup down
The most common cause in real sheets: the formula works in the first row, and some rows below show #N/A although the products are in the list.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Order | Price | ||
| 2 | Apple | 1.2 | Apple | 1.2 | ||
| 3 | Pear | 1.5 | Plum | 0.8 | ||
| 4 | Plum | 0.8 | Pear | #N/A | ||
| 5 | Bread | 2.4 | Milk | 1.1 | ||
| 6 | Milk | 1.1 | Apple | #N/A |
#N/A The value you looked for is not in the lookup range.Click F4: its table range is A4:B8, two rows lower than F2's. Filling the formula down moved the range with it, so Pear (row 3) and Apple (row 2) fell out of it. F3 and F5 work only because Plum and Milk are still inside their ranges. Click F2 and change the range to $A$2:$B$6: the whole column follows and every price appears. The $ signs lock the range, see absolute references.
#N/A because of extra spaces
"Pear " with a trailing space and "Pear" are different values to Excel. Spaces come from data typed by hand, copied from web pages, or exported from other systems, and they are invisible in the cell.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Look for | Price | Length of A2 | |
| 2 | Pear | 1.5 | Pear | #N/A | 5 | |
| 3 | Apple | 1.2 | ||||
| 4 | Plum | 0.8 |
#N/A The value you looked for is not in the lookup range.E2 returns #N/A. F2 shows the cause: Pear has 4 letters, but A2 is 5 characters long. Delete the space in A2 and the lookup works. Three ways to fix it for good:
- Clean the column: put
=TRIM(A2)in a helper column, fill it down, then copy it and Home > Paste > Values over the original. - Trim the lookup value when the spaces are in what you type:
=VLOOKUP(TRIM(D2),A2:B4,2,FALSE). - Trim the whole lookup column inside the formula (Excel 2021 or Microsoft 365):
=XLOOKUP(D2,TRIM(A2:A4),B2:B4).
Text pasted from web pages can contain a non-breaking space, which TRIM does not remove. The TRIM page shows how to replace it with SUBSTITUTE(A2,CHAR(160)," ").
#N/A when the value is not in the first column
VLOOKUP searches only the first column of its range and returns a column to the right of it. Looking up a value from any other column returns #N/A, even when it is in the table.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Code | Price | Code | VLOOKUP | XLOOKUP | |
| 2 | Apple | A-17 | 1.2 | P-22 | #N/A | Pear | |
| 3 | Pear | P-22 | 1.5 | ||||
| 4 | Plum | P-31 | 0.8 | ||||
| 5 | Bread | B-05 | 2.4 | ||||
| 6 | Milk | M-40 | 1.1 |
#N/A The value you looked for is not in the lookup range.The codes are in column B, so VLOOKUP on A2:C6 looks for P-22 among the product names and fails. It also cannot return the product name, which sits before the code. XLOOKUP takes the lookup column and the return column separately and finds Pear. In Excel 2019 and earlier, =INDEX(A2:A6,MATCH(E2,B2:B6,0)) does the same.
#N/A from numbers stored as text
An order number typed as text ('1001, or imported from a CSV) never matches the number 1001, and the other way round. Both cells show 1001, so this one is hard to see. In Excel:
A2:B6 holds order numbers stored as text, E2 holds the number 1001
=VLOOKUP(E2,A2:B6,2,FALSE) #N/A
=VLOOKUP(E2&"",A2:B6,2,FALSE) found: E2&"" turns the number into text
A2:B6 holds real numbers, E2 holds "1001" as text
=VLOOKUP(E2,A2:B6,2,FALSE) #N/A
=VLOOKUP(--E2,A2:B6,2,FALSE) found: -- turns the text into a number
=ISTEXT(A2) tells you which side is text, and a small green triangle in the corner of a cell marks a number stored as text. To convert a whole column, see text to number.
IFNA or IFERROR: show a message when nothing is found
When a value can legitimately be missing, show something more useful than #N/A. Use IFNA, not IFERROR:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Look for | IFNA | IFERROR | ||
| 2 | Apple | 1.2 | Pear | #REF! | Not found | ||
| 3 | Pear | 1.5 | |||||
| 4 | Plum | 0.8 | |||||
| 5 | Bread | 2.4 | |||||
| 6 | Milk | 1.1 |
#REF! The formula points at a cell that does not exist.Both formulas ask for column 3 of a two-column range, a mistake. IFNA lets the #REF! through, so you see the bug. IFERROR hides it and says "Not found" for Pear, which is in the list. Change both 3s to 2: now each shows 1.5, and with E2 set to Kiwi each shows "Not found". XLOOKUP has the message built in: =XLOOKUP(E2,A2:A6,B2:B6,"Not found"). More on the difference in IFERROR.
Fix a lookup broken by spaces
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Look for | Price | ||
| 2 | Apple | 1.2 | Plum | |||
| 3 | Pear | 1.5 | ||||
| 4 | Plum | 0.8 | ||||
| 5 | Bread | 2.4 | ||||
| 6 | Milk | 1.1 |
Your turn: Every product in the list was imported with a space at the end, so =VLOOKUP(E2,A2:B6,2,FALSE) returns #N/A. Write a formula in F2 that still returns the price of the product in E2.
=XLOOKUP(E2,TRIM(A2:A6),B2:B6) trims the list inside the formula. =VLOOKUP(E2&" ",A2:B6,2,FALSE) also works here, but only as long as every product has exactly one trailing space; cleaning the column with TRIM is the fix that lasts.
Frequently Asked Questions
What does #N/A mean in Excel?
N/A stands for "not available". It means a lookup function (VLOOKUP, HLOOKUP, XLOOKUP, MATCH, XMATCH) could not find the value it was given. =NA() also returns it on purpose, for example so a chart skips a point instead of plotting it as 0.
Why does VLOOKUP return #N/A when the value exists?
The two values are not exactly the same. Common reasons: a trailing space in one of them, a number stored as text in one and as a number in the other, or a table range without $ that slid down when the formula was filled, so the row with the value is no longer inside it.
Why does VLOOKUP return #N/A for some rows but not others?
The table range was not locked before the formula was filled down, so in each row it starts one row lower: A2:B6 in the first row becomes A4:B8 two rows later, and values above the range are no longer found. Lock it with $: =VLOOKUP(E2,$A$2:$B$6,2,FALSE).
Why does XLOOKUP return #N/A?
The value is not in the lookup array, or differs from it by a space or by being text instead of a number. XLOOKUP matches exactly by default, so nothing close is accepted. Its fourth argument replaces the error: =XLOOKUP(E2,A2:A6,B2:B6,"Not found").
Why does MATCH return #N/A?
With match type 0 the value is not in the range, exactly as with VLOOKUP. With match type 1 or omitted, the range must be sorted ascending and the value must not be smaller than its first item; use =MATCH(E2,A2:A6,0) for an exact match.
