Fix MATCH Formula

[Rupert Bennett]

Can you help me fix this formula. It resides on a summary page and pulls information from another page called "BenefitsUsed" It works well until there is no match in the range on the "BenefitsUsed" for a name appearing in column "D" on the summary page. I get the #N/A error. I would like "0" to appear instead. Here is the formula:

=IF(ISBLANK(D26),0,IF(MATCH(D26,BenefitsUsed!$C$8:$C$65532,0),SUMPRODUCT(--(BenefitsUsed!$C$8:$C$65532=D26),--(YEAR(BenefitsUsed!$D$8:$D$65532)=YEAR(TODAY())),(BenefitsUsed!$E$8:$E$65532)),0))
Thanks for helping
Rupert

 

[Andrew Poulsom]

Maybe try:

=IF(ISBLANK(D26),0,IF(ISNUMBER(MATCH(D26,BenefitsUsed!$C$8:$C$65532,0)),SUMPRODUCT(--(BenefitsUsed!$C$8:$C$65532=D26),--(YEAR(BenefitsUsed!$D$8:$D$65532)=YEAR(TODAY())),(BenefitsUsed!$E$8:$E$65532)),0))

 

[Aladin Akyurek]

Can you help me fix this formula. It resides on a summary page and pulls information from another page called "BenefitsUsed" It works well until there is no match in the range on the "BenefitsUsed" for a name appearing in column "D" on the summary page. I get the #N/A error. I would like "0" to appear instead. Here is the formula:

=IF(ISBLANK(D26),0,IF(MATCH(D26,BenefitsUsed!$C$8:$C$65532,0),SUMPRODUCT(--(BenefitsUsed!$C$8:$C$65532=D26),--(YEAR(BenefitsUsed!$D$8:$D$65532)=YEAR(TODAY())),(BenefitsUsed!$E$8:$E$65532)),0))
Thanks for helping
Rupert

I don't understand why you need the IF/MATCH bit...

=IF(D26="",0,SUMPRODUCT(--(BenefitsUsed!$C$8:$C$65532=D26),--(YEAR(BenefitsUsed!$D$8:$D$65532)=YEAR(TODAY())),BenefitsUsed!$E$8:$E$65532))

should suffice.

 

[Rupert Bennett]

This did it. My attempts to use IF(ISNA) and IF(ISERROR) were were futile and I just could not get it to work. Thanks for helping.
Rupert

 

[Rupert Bennett]

The IF/MATCH was used because column"D" on the summary page will have blank cells and also names in column "D" did not necessarily have to appear on the "BenefitsUsed" page. Maybe it was not necessary, but it worked, other than when it did not find a match on the "BenefitsUsed" Page. It could be that this is where my problem is. I will test this extensively. Thanks, again, for all the help.
Rupert