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...
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))
=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.
- I just figured it out, XLOOKUP to the rescue:
=XLOOKUP(C$1,'Product Pricing'!$C$1:$M$1,XLOOKUP($A2&$B2,'Product Pricing'!$A$2:$A$129&'Product Pricing'!$B$2:$B$129,'Product Pricing'!$C$2:$M$129))