Vlookup
Posted by Geoff on January 18, 2002 5:48 AM
In excel, when you do a Vlookup and it brings back a N/A, how do you have it bring 0 instead?
Thanks....
Posted by Tom in DaBurg on January 18, 2002 5:58 AM
Here is a working example of a Vlookup with the ISNA function to return a 0 instead of the #NA
you can adapt this to your sheet.
Tom
Posted by Tom in DaBurg on January 18, 2002 6:00 AM
Sorry would have helped if I gave the sample.
its still early, someone get my coffee please!
=IF(ISNA(VLOOKUP(F10,$R$3:$S$11,2)),0,VLOOKUP(F10,$R$3:$S$11,2))
Posted by Eric B on January 18, 2002 6:03 AM
Posted by Aladin Akyurek on January 18, 2002 6:30 AM
Geoff --
If you want to do an exact match, I'd suggest using:
=IF(COUNTIF(E2:E10,A1),VLOOKUP(A1,E2:G10,3,0),0)
If an exact match is not needed, you can either set up your lookup table such a way that the VLOOKUP formula can return a 0 value for some out of bound lookup-values or use one of:
=IF(AND(lookup-value >= X, lookup-value <= Y), VLOOKUP(lookup-value, lookup-table, column-num-lookup-table),0)
=IF(ISNUMBER(MATCH(A1,E2:E10)),VLOOKUP(A1,E2:G10,3),0)
where A1 houses the lookup-value of interest, E2:G10 the lookup-table.
Aladin
========
