# 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

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 &LT;= 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.