Excel 2000 question

[ulicki]

How do I get a vlookup to return 0 (zero) when the value "#N/A" is returned?

My formula is =VLOOKUP(S8,$C$8:$N$3448,12,FALSE) which returns a currency value if there is a match. When I do not get a match I would like to have the value zero returned.

Thanks,

Mike

 

[dhulbert]

Mike

Use the following.

=if(iserror(VLOOKUP(S8,$C$8:$N$3448,12,FALSE)),0,VLOOKUP(S8,$C$8:$N$3448,12,FALSE))

 

[ulicki]

Thanks!!!, it worked

 

[KenWright]

You should really use =IF(ISNA(Your_formula),0,your_formula) as opposed to =IF(ISERROR(Your_formula),0,your_formula)

Both will do the trick, but the ISERROR will prevent errors that should be alerting you to something being wrong from being displayed. The ISNA() will specifically trap just the error you need to catch.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ulicki]

Thanks Ken,

I will change my formula.