I have over 6000 records and half of them are formulas that are missing a variable so they result in #N/A on the spreadsheet, what i want to do is if the cell is #N/A then leave the cell blank, otherwise print a string like so

=IF(AR6347="#N/A","","string in here")

But this does not work with ="#N/A", is there a way to do this?

5 Answers

Try using the ISNA() function:

=IF(ISNA(AR6347),"","string in here")

7

In Excel 2007 and later you're able to use:

=IFERROR(A1;"")

to replace ="#N/A" or any other error with empty string.

Use the iserror() function. For instance, with a vlookup not finding a value in my table, I want to display Not found instead of #N/A, then I type the following:

=if(iserror(vlookup(A1,Sheet2!$A$1:$C$360,3,0)),'Not found',vlookup(A1,Sheet2!$A$1:$C$360,3,0))

So, this formula is just saying: if the vlookup function is retrieving an error, then return the string 'Not found', else return the result of the vlookup function.

0

SIMPLEST METHOD

You can use this directly in the cell with the formula if you want to skip the intermediate cell steps

=IFNA(formula,"text/value if formula result is #N/A")

This will put the result of the formula in the cell (if the result is not #N/A) and will put the text string (or whatever value you put as the second argument) in the cell instead if the formula result is #N/A.

I use it with VLOOKUP and INDEX-MATCH all the time when I don't want the #N/A's to show. I replace what would be an #N/A result with a blank cell ("") or zero(0) or text ("text string") as needed.

I used something similar to determine if an item in A matched one in D and not display #N/A. Used for presentation purposes. =IF(IFERROR(MATCH(A4,$D$2:$D$11,0),0)>0,"text for TRUE","text for FALSE")

1