Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 9 down vote accepted

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.

share|improve this answer

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

share|improve this answer
1  
...but this is not available in OpenOffice. – Arjan Dec 17 '14 at 18:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .