MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Eric J on April 12, 2001 7:19 AM

My question is when you are doing a vlookup and it doesn't find the value you are searching for it returns an #n/a. Is there a way instead of returning an #n/a can you have it return a 0?

Posted by Dave Hawley on April 12, 2001 7:38 AM

Hi Eric

There are a few ways to do this, but the one I prefer for ease of reading is:



OzGrid Business Applications

Posted by Richie Turner on April 12, 2001 7:44 AM

Use the ISNA(expression) function in an IF statement, ISNA returns true if the expression is true.

eg. IF(ISNA(your vlookup),0,(vlookup...))

In the above example if "your vlookup" returns #N/A Excel will display a zero, otherwise Excel will carry out the vlookup as normal.

Hope this helps. E-mail me if you need further explanation


Posted by Malcolm Robertson on April 13, 2001 2:37 AM

=If(ISNA(VLOOKUP... Works fine but is want to save time use the match function it's works faster on great blocks of data.


Posted by Aladin Akyurek on April 13, 2001 3:41 AM



You can't use MATCH with an m X n matrix where m>1 and n>1.
I think you're hinting at the following use:




By the way, it appears to me as a pertinent suggestion.