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 not.

Can you help?

Answer 1

I just had the exact same question and come up with this:

http://www.linuxtopia.org/online_books/office_guides/openoffice_calc_user_guide/openoffice_calc_Informational_functions.html

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.