Forum Discussion
Why is a multi criteria index match working for US, one CA, and no other CA?
I have a file where Sheet 1 has Column A (Row 2) "Product#", Column B (Row 2) "Country", and Row 1 (Column C through M) MM/YYYY "06/2021, 07/2021, etc.". In Sheet 2 I have the same setup but I have d...
Caine_Firstbase I believe the problem is that you are multiplying the 2 MATCH results:
=INDEX('Product Pricing'!$C$2:$M$129,MATCH('Product Total Dollars'!$A2,'Product Pricing'!$A$2:$A$129,0)*MATCH('Product Total Dollars'!$B2,'Product Pricing'!$B$2:$B$129,0),MATCH(C$1,'Product Pricing'!$C$1:$M$1,0))
So the US will MATCH at row 1 so it works but CA will MATCH at 3 and the PRODUCT # is at row 3 so that is 9 and then for PRODUCT at 4 it looks at index 12 and that probably doesn't exist and hence the #REF! error. Try this instead:
=INDEX('Product Pricing'!$C$2:$M$129,
MATCH('Product Total Dollars'!$A2 & 'Product Total Dollars'!$B2,'Product Pricing'!$A$2:$A$129 & 'Product Pricing'!$B$2:$B$129,0),
MATCH(C$1,'Product Pricing'!$C$1:$M$1,0))
Or better yet consider using FILTER of dynamic arrays (spill over) using Index:
=INDEX('Product Pricing'!$C$2:$M$129,
MATCH('Product Total Dollars'!$A2 & 'Product Total Dollars'!$B2,'Product Pricing'!$A$2:$A$129 & 'Product Pricing'!$B$2:$B$129,0),
)
So the whole row of data will get filled instead on repeating that search.
You can share a link to your worksheet using OneDrive or if you want to send the worksheet you can DM it to me (click my name and then click Message in my profile).
- Unfortunately the version that I am running on Mac does not allow me to use FILTER since the function does not exist in this version. I tried the other method to no avail. For some reason the & does not work in my Excel which is why I used *.
- how about using SUMPRODUCT instead. Something like:
=INDEX('Product Pricing'!$C$2:$M$129,
SUMPRODUCT(
('Product Total Dollars'!$A2 = 'Product Pricing'!$A$2:$A$129) *
('Product Total Dollars'!$B2 = 'Product Pricing'!$B$2:$B$129,0) *
ROW('Product Pricing'!$A$2:$A$129))-1,
MATCH(C$1,'Product Pricing'!$C$1:$M$1,0))- This is not working either, I sent you a private message with the file so that you could look into the file as well and see if you can get it to work on your end.
