Hi there,
Struggling with a lookup formula that wizard produced....
=INDEX($AS$9:$AT$33, MATCH(B4,$AS$9:$AS$33,), MATCH("PERCENTAGE",$AS$9:$AT$9,))
That's all fine but some of the items in the list will not be able to match the lookup table and in this case I want it to say 0 (zero), but instead its saying #N/A which I understand means data unavailable. Is there a way of saying if so and so doesn't match put 0 instead of #N/A?
Thanks!!!!!!
Last edited by VBA Noob; 11-13-2008 at 08:52 AM.
Wrap that whole formula in an IF(ISERROR) trap.
=IF(ISERROR(INDEX($AS$9:$AT$33,MATCH(B4,$AS$9:$AS$33,),MATCH("PERCENTAGE",$AS$9:$AT$9,))),0,INDEX($AS$9:$AT$33,MATCH(B4,$AS$9:$AS$33,),MATCH("PERCENTAGE",$AS$9:$AT$9,)))
Last edited by JBeaucaire; 11-13-2008 at 07:26 AM. Reason: Changed blank result to "0" as requested by OP
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
If you know the cells that's empty and causing the problem, you could just check that cell first to make the trap shorter. You decide. If you can test a shorter range, test that instead. For instance maybe that cell B4 is empty...
=IF(ISBLANK(B4),0,INDEX($AS$9:$AT$33,MATCH(B4,$AS$9:$AS$33,),MATCH("PERCENTAGE",$AS$9:$AT$9,)))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank you, I'll give them both a go and get back to you if I need more help!
It worked!!! Thanks!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks