Problems With Index, Match Array Formula

[ccsher77]

Hi everyone,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am struggling with an issue regarding the Index, Match Array Formula below.<o></o>
<o></o>
Basically the formula is to check Certain columns and return data based on matches in those columns.<o></o>
The formula works well but in cells where the formula does not find a result it shows # N/A. <o></o>
Which in turn makes it impossible for me to calculate the sum, average etc of any column that has this #N/A in it.<o></o>
<o></o>
Is there a way that I can amend the formula to show a zero rather than #N/A without compromising the formula?<o></o>
<o></o>
{=INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=$N$1)*($B$1:$B$100=H4),0))}<o></o>
<o></o>
Thanks for any help in advance!!

Craig.

 

[Jonmo1]

Try

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=$N$1)*($B$1:$B$100=H4),0))))


Still entered as array with CSE

Hope that helps.

 

[T. Valko]

Hi everyone,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am struggling with an issue regarding the Index, Match Array Formula below.<o></o>
<o></o>
Basically the formula is to check Certain columns and return data based on matches in those columns.<o></o>
The formula works well but in cells where the formula does not find a result it shows # N/A. <o></o>
Which in turn makes it impossible for me to calculate the sum, average etc of any column that has this #N/A in it.<o></o>
<o></o>
Is there a way that I can amend the formula to show a zero rather than #N/A without compromising the formula?<o></o>
<o></o>
{=INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=$N$1)*($B$1:$B$100=H4),0))}<o></o>
<o></o>
Thanks for any help in advance!!

Craig.

If you're using Excel 2007 or later try it like this...

Still array entered:

=IFERROR(INDEX($C$1:$C$100,MATCH(1,IF($A$1:$A$100=$N$1,IF($B$1:$B$100=H4,1)),0)),0)

 

[ccsher77]

Thank you both for your Quck reply.

Both Worked very effectivley and has saved me a huge amount of

Frustration!!!

Thanks Once Again!!

 

[T. Valko]

Thank you both for your Quck reply.

Both Worked very effectivley and has saved me a huge amount of

Frustration!!!

Thanks Once Again!!

You're welcome. Thanks for the feedback!