How do I test if a cell in OpenOffice Calc contains #N/A?

Question

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.

Solution 1

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.

Solution 2

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" )

Comments

[Arjan]

...but this is not available in OpenOffice.

[adhominem]

Just for the record, LibreOffice has implemented IFERROR since 4.0