Former employees strange match formula syntax

Hi guys,

I have a very large and cumbersome legacy spreadsheet that doesn't seem to be doing what its supposed to. The creator passed away and I cannot figure out a couple of match formulas that he's got in a larger sum product formula. At this stage I want to understand why he's written these match formulas this way...

=MATCH('FY20 SALEABLE TOTAL'!$A$10:$A$62,'FY20 UNITS BY DAY'!$E42,0)

He has an array as the look-up and a single cell as the array . The results of the match when written separately are #NA and what it ends up trying to match out of the array is unpredictable, the formula doesn't even make sense for the rest of the formula if you switch them around either. Just as in case the whole formula can provide a clue it is:

=IFERROR(SUMPRODUCT(--ISNUMBER(MATCH('FY20 SALEABLE TOTAL'!$A$10:$A$62,'FY20 UNITS BY DAY'!$E42,0)*MATCH('FY20 SALEABLE TOTAL'!$E$7:$BE$7,'FY20 UNITS BY DAY'!F$2,0)),'FY20 SALEABLE TOTAL'!$E$10:$BE$62)*GETPIVOTDATA("sum of KGs",'PACKED %'!$B$1,"TRXDATE",F$4)*GETPIVOTDATA("sum of OM",PRODUCTS!$A$3,"ITEMNMBR",$B42),0)

Can anybody spot what I am missing?

=MATCH('FY20 SALEABLE TOTAL'!$A$10:$A$62,'FY20 UNITS BY DAY'!$E42,0) will return an array of 53 matches for the 53 cells in the criteria., by reversing it, =MATCH('FY20 UNITS BY DAY'!$E42,'FY20 SALEABLE TOTAL'!$A$10:$A$62,0) you only get 1 match.

The way the formula is constructed, the first match looks for all of the rows that meet 1 criteria, rather than just a single match. The second match does the same for the columns. The true matches are then multipled by the values in the range 'FY20 SALEABLE TOTAL'!$E$10:$BE$62 to give the final sumproduct total.

Finally the sumproduct total is multipled by the getpivot results.

If the results of the formula are not as expected, the evaluation tool can be your best friend. Select the cell with the formula, then press Alt m v.

Thanks jason, I haven't used the Match formula that way before so thanks for the easy to understand answer. I went over it with the Evaluation tool but (as I now understand) It was just displaying a large range of N/As for the 53 matches which was throwing me. I'm quicker at knocking up a bit of vba than writing these long formulas so I normally just do that if a formula is getting too hard.