# Fix MATCH Formula

#### Rupert Bennett

##### Active Member
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Andrew Poulsom

##### MrExcel MVP
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
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
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
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

Replies
17
Views
468
Replies
3
Views
663
Replies
3
Views
102
Replies
0
Views
87
Replies
3
Views
332

Threads
1,182,038
Messages
5,933,366
Members
436,891
Latest member
DanDan

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

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