MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookup


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:

=IF(ISNA(VLOOKUP(255,A1:C16,3,FALSE)),0,VLOOKUP(255,A1:C16,3,FALSE))

Dave

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

Richie

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.

=IF(ISERROR(MATCH(255,A1:C16,0)),0,VLOOKUP(255,A1:C16,3,FALSE))


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


====================================

Malcolm

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:

=IF(ISERROR(MATCH(255,A1:A16,0),0,VLOOKUP(255,A1:C16,3,0))

Or:

=IF(ISNUMBER(MATCH(255,A1:A16,0)),VLOOKUP(255,A1:C16,3,0),0)

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

Aladin