Index/Match combined with Sumproduct #N/A Issue

Hello all,

I created a formula that I was hoping could reduce a few steps in the process I take to match monthly discounts that were created with their corresponding account numbers, and attempted to use an Index/Match formula with an embedded Sumproduct formula. I would understand if this didn't work at all for one reason or another, but out of the 20 accounts in my example worksheet it works for about 75% of them. I cannot for the life of me figure out what it is about the remaining accounts that is causing the formula to produce #N/A instead. I am guessing it might have something to do with Match functions issues with sorting.

In the example workbook I've attached one tab shows the current process that produces 100% accurate results, and then the other tab is what I'm trying to replace it with and it shows that the highlighted column has some accounts that the formula works for and some that it doesn't.

Any advice or suggestions are appreciated, I am open to modifying my approach to this process dramatically if something else would work better. Thanks!

Try

=INDEX($H$3:$I$22,MATCH(SUMPRODUCT(($A$3:$A$36=A3)*$D$3:$E$36),$H$3:$H$22,-1),2)

The values in I are in descending order.

Hey guys,

Thanks for both replies. I tried sorting column I in descending order then changing the Match Type to -1, which corrected the #N/A values but then caused incorrect credit memos to be matched with account numbers.

Inserting the Round function into the formula seems to have solved the problem. I stepped into the formula and the Sumproduct was producing the correct number for all intents and purposes, but it must have someone added a trailing decimal that was causing issues with the exact match Match Type.

Thanks again, marking this as Solved!