Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

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?

Check out our Excel Resources

Re: Vlookup

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

Re: Vlookup

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

Re: Vlookup

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

MATCH in service of VLOOKUP

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.