I am using the vlookup function. I want it to look up an exact match then
return the number zero if it does not find a match instead of #N/A.

Can you help?

Hi Fitzi,

=IF(ISNA(VLOOKUP(C2,$A$2:$B$20,2,FALSE)),"",VLOOKU P(C2,A2:B20,2,FALSE))


Not above is one line if it should break when posted.

Assumes:
C2 is the Lookup Value
A2:B20 is the table array

If lookup returns #N/A the a null ("") is used in lieu.

--
Regards,

OssieMac


"Fitzi" wrote:

I am using the vlookup function. I want it to look up an exact match then
return the number zero if it does not find a match instead of #N/A.

Can you help?

Hi again,

My apologies. So used to returning null if not found I did it in the
formula. Should be following to return zero if not found:

=IF(ISNA(VLOOKUP(C2,$A$2:$B$20,2,FALSE)),0,VLOOKUP (C2,A2:B20,2,FALSE))

--
Regards,

OssieMac


"Fitzi" wrote:

I am using the vlookup function. I want it to look up an exact match then
return the number zero if it does not find a match instead of #N/A.

Can you help?

And don't forget to make the second lookup table absolute also.

=IF(ISNA(VLOOKUP(C2,$A$2:$B$20,2,FALSE)),0,VLOOKUP (C2,$A$2:$B$20,2,FALSE))


Gord Dibben MS Excel MVP


On Fri, 30 Nov 2007 22:30:00 -0800, OssieMac
wrote:

Hi again,

My apologies. So used to returning null if not found I did it in the
formula. Should be following to return zero if not found:

=IF(ISNA(VLOOKUP(C2,$A$2:$B$20,2,FALSE)),0,VLOOKU P(C2,A2:B20,2,FALSE))

Thanks for correcting that Gord. What would we do without observant people
like yourself looking over our shoulder and fixing our mistakes?

--
Regards,

OssieMac


"Gord Dibben" wrote:

And don't forget to make the second lookup table absolute also.

=IF(ISNA(VLOOKUP(C2,$A$2:$B$20,2,FALSE)),0,VLOOKUP (C2,$A$2:$B$20,2,FALSE))


Gord Dibben MS Excel MVP


On Fri, 30 Nov 2007 22:30:00 -0800, OssieMac
wrote:

Hi again,

My apologies. So used to returning null if not found I did it in the
formula. Should be following to return zero if not found:

=IF(ISNA(VLOOKUP(C2,$A$2:$B$20,2,FALSE)),0,VLOOKU P(C2,A2:B20,2,FALSE))

Just passing on the help I get when I make my typos<g

Gord

On Fri, 30 Nov 2007 23:41:01 -0800, OssieMac
wrote:

Thanks for correcting that Gord. What would we do without observant people
like yourself looking over our shoulder and fixing our mistakes?