MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

========