How do I test if a cell in OpenOffice Calc contains #N/A?
I'm using the VLOOKUP
function to lookup values in a column based upon a search query. But since it's not a given that a value always exists for my search parameter, sometimes VLOOKUP
returns #N/A
.
How do I write an IF
function that tests if VLOOKUP
returns #N/A
?
I've read somewhere that #N/A
is just a text string, so I've obviously tried:
=IF(B1="#N/A";"Not found";B1)
But with no luck. So maybe it's not.
I just had the exact same question and found this page about openoffice functions.
There is a function ISNA(value) which should do the trick. In your example, the following should work:
=IF(ISNA(B1);"Not found";B1)
In my case, I wanted to check whether a cell is empty or not, this is done by ISBLANK(value)
, just for the record.
I always use "IFERROR" in these cases. I like everything to be pretty, so unless I'm debugging my spreadsheet, I almost always throw an "IFERROR" at the beginning of all my formulas. It will return your "else value" if the function errors, otherwise it will return the normal function value.
=IFERROR( VLOOKUP(xxxxx) , "Not Found" )