Fix MATCH Formula

Rupert Bennett

Active Member
Joined
Nov 20, 2002
Messages
271
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Rupert Bennett said:
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

Active Member
Joined
Nov 20, 2002
Messages
271
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

Active Member
Joined
Nov 20, 2002
Messages
271
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
 

Forum statistics

Threads
1,148,222
Messages
5,745,467
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top