Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2000 question

Status
Not open for further replies.

ulicki

Technical User
Oct 24, 2001
88
US
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
 
Mike

Use the following.

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

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

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Thanks Ken,

I will change my formula.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top