Vlookup issue
Posted by Shaggy on May 08, 2001 11:13 AM
I'm trying to write a vlookup command which will look up data that isn't available yet.
This has caused me to get a '#n/a' response.
Is there anyway to force a null value if the match isn't located?
Here is the code I'm using:
=VLOOKUP(H15,'Z300'!AK$4:AM$155,2,0)
Posted by Barrie Davidson on May 08, 2001 11:24 AM
Try
=IF(ISERROR(VLOOKUP(H15,'Z300'!AK$4:AM$155,2,0)),"",VLOOKUP(H15,'Z300'!AK$4:AM$155,2,0))
Barrie
Posted by Aladin Akyurek on May 08, 2001 11:26 AM
Try:
=IF(ISNUMBER(MATCH(H15,'Z300'!AK$4:AK$155,0),VLOOKUP(H15,'Z300'!AK$4:AM$155,2,0),"")
By the way:
If you use this formula in the same workbook where your lookup table is, I'd suggest that you give a name to the range AK$4:AK$155, e.g., LVALUES (for lookup values) via the Name Box or via the option Insert|Name|Define. In the same vein, name the range AK$4:AM$155 DATA for example. The formula will look imho much nicer:
=IF(ISNUMBER(MATCH(H15,LVALUES,0),VLOOKUP(H15,DATA,2,0),"")
Side Note. Mark -- MS should take that VLOOKUP proposal we discussed a while ago seriously...
Aladin
Posted by kevin on May 08, 2001 11:27 AM
You may want to try nesting your vlookup formula within an if statement. You can create an if statement that will return a null value (or whatever value you choose really) if the result of your vlookup statement will be an error message, and return the lookup value you want if the result of the vlookup formula is NOT an error message. This is an example of what your formula would look like:
IF(ISERROR(VLOOKUP(lookup_value,table_array,col_index_num,range_lookup))=TRUE,0,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup))
This formula will return 0 if the result is an error message, but will find your data if there is no error message. Hope this helps
kevin
Posted by Mark W. on May 08, 2001 11:44 AM
> Side Note. Mark -- MS should take that VLOOKUP proposal we discussed a while ago seriously...
I'm not so sure. I'd wouldn't replace #N/A with
"" in my worksheets. I'd handle the exception
in the formula that was effected by #N/A instead.
Posted by IML on May 08, 2001 12:02 PM
Aladin,
If you are talking about the proposal you made for an additional argument in Vlookup that would allow you to specify a value when there is no match (which defaults to #N/A!), I would second that (in case anyone from MS is listening!)
Ian
Posted by Aladin Akyurek on May 08, 2001 12:08 PM
Yeah, that's the one
Mark isn't convinced yet...
Posted by Mark W. on May 08, 2001 12:23 PM
MS "Wish" Submittal Form
Aladin, Go for it!
http://register.microsoft.com/regsys/custom/wishwizard.asp?fu=http://www.microsoft.com/mswish/thanks.htm
Posted by Aladin Akyurek on May 08, 2001 12:47 PM
Re: MS "Wish" Submittal Form --- Done.
Posted by Sean on May 08, 2001 3:00 PM
I could also suggest =IF(ISNA...same as Barrie
The ISNA gives one small advantage that if you have messed up in some otherway eg #Div0 you will still get that error message whereas the ISERROR will come up with blanks for all errors rather than this one expected error.
Sean
s-o-s@lineone.net
Posted by Barrie Davidson on May 09, 2001 9:43 AM
Very good point.
