I've been trying various versions of Index(Match and Sumproduct(Match but everything has failed. Basically I have Revenue amounts in another spreadsheet in a format that isn't usable for uploading into another software so I'm trying to standardize the format.

I'm trying to pull all the Apple data from each Region, Brand, Date Bucket, Year, and Quote Type, same for Oranges and Pears.

I've tried these and they've failed for different reasons. The first one does alright until I add a fourth criteria, so it works for Region, Brand and Date Bucket, but when I add Year, it fails. And I'm also going to have to add Quote Type. Please note that the reference cells below are for different spreadsheets and I tweaked them earlier today to fit my current spreadsheet, so please disregard the Row and column references.

'=INDEX('Practice Data'!$J$2:$J$2826,MATCH('Index Match 2 Items'!A8&'Index Match 2 Items'!B8,'Practice Data'!$D$2:$D$2826&'Practice Data'!$I$2:$I$2826,0))
'=INDEX($C$8:$J$12,MATCH($M7,$B$8:$B$12,0),MATCH($N7,$C$7:$J$7,0))
'=sumproduct(--(A2:A200=term),--(B2:B200=customer),--(C2:C200=grade),offset(C1,1,match(date,D1:H1),199,1))


Multi match.PNG

The BP in this image is B, excel keeps auto-filling and I'm tired and missed it.

Multi match Ref File.PNG