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
