Replacing #N/A result with zero or with text message, in Excel

Displaying a neat result table without the #N/A text inserted automatically by the Excel can be done by using the IF function with the ISNA function.

The syntax of the ISNA function is:
=ISNA(cell location)

If the content of the cell at ‘cell location’ is #N/A the function returns TRUE otherwise FALSE. The idea is to use IF function to replace the content of the cell based on the TRUE/FALSE result received from the ISNA function.

Simple Examples
Let’s say that cell F5 contains the following formula:
=IF(ISNA(B5),0,B5)
In this example if cell B5 is #N/A then the IF function will display in cell F5 the number 0 (zero).
When B5 is not #N/A, the value in cell F5 will be the same as B5. It could be a number or a letter or text.

=IF(ISNA(B5),"Entry is out of range","Entry is OK")
The above formula was written in cell F5.
In this example when cell B5 is #N/A then cell F5 will display the message “Entry is out of range". When B5 is not #N/A then F5 will display the message “Entry is OK”.

Advance Example
There are two tables and cell F5 for message display, as in the below picture.

The content of cell A5 is entered by the user and should be one letter between A to D.

The value of cell B5 in Table-1 is taken from column B of Table-2 with LOOKUP function depends on the entry of user's input in cell A5. The content of B5 is:
=VLOOKUP(A5,A9:B12,2,0)

Cell C5 contains the number 1.

Cell D5 sums the value of B5 and C5.
=B5+C5

Cell F5 displays text message that gives feedback about the data that was entered in A5, if the data is A to D, then F5 will display “Entry is OK”, for any other input F5 will display “Entry is out of range”

Table-1 and Table-2 can be placed on different sheets or workbook.

When someone enters the letter E in cell A5 then Table-1 will look like this:

Cells B5 and D5 will show #N/A. Since the input data E can’t be found in Table-2, the VLOOKUP function in cell B5 will return #N/A and the formula in D5 will not be able to do the sum of B5+C5 thus will also return #N/A.

Let’s say that column B in Table-2 can contains only positive numbers except zero (0). We can replace now the #N/A in cell B5 with 0. This will enable Excel to do the sum in D5 and this will indicate to F5 that the input data was out of range.

To replace #N/A in B5 with zero, we will change the content of B5 to the following:
=IF(ISNA(VLOOKUP(A5,A9:B12,2,0)),0,(VLOOKUP(A5,A9:B12,2,0)))

After this change when the input data in A5 will cause #N/A in B5, the above IF function will replace it to 0.

As can be seen in the above picture, replacing the #N/A of cell B5 with zero (0) enabled Excel to do the sum in D5 though cell D5 shows now 1 which is the sum of 0 (B5) + 1 (C5).

Pay attention that the input data E is out of range but F5 message says “Entry is OK” since cell F5 checks cell B5 for not having #N/A but now B5 has the number zero so the check pass to display the positive message “Entry is OK”.

In order to correct the message displayed in F5 we will add IF function in the existing IF function and the content of cell F5 will be:
=IF(ISNA(B5),"Entry is out of range",IF(B5=0,"Entry is out of range","Entry is OK"))
The message in F5 will show now the correct message as in the below picture:

* * *