MrExcel Publishing
Your One Stop for Excel Tips & Solutions


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?


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.


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!


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 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)


where A1 houses the lookup-value of interest, E2:G10 the lookup-table.