Why is a multi criteria index match working for US, one CA, and no other CA?

[Caine_Firstbase]

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 datapoints (numbers) so it is setup like a matrix.

Sheet 1

Product#	Country	06/2021	07/2021	08/2021
A	US	Formula Works (5)	Formula Works (4)	Formula Works (3)
B	US	Formula Works (3)	Formula Works (4)	Formula Works (5)
C	CA	Formula Works (1)	Formula Works (2)	Formula Works (3)
D	CA	Formula Fails	Formula Fails	Formula Fails

Sheet 2

Product#	Country	06/2021	07/2021	08/2021
A	US	5	4	3
B	US	3	4	5
C	CA	1	2	3
D	CA	#REF!	#REF!	#REF!

In Sheet 1 I am trying to pull in a value from Sheet 2 where if the reference in Column A and Column B and Row 1 matches, pull the subsequent value. I used an INDEX MATCH formula to complete this:

=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))

I was able to get this to work for all of the US data identified in column B and the first instance for the CA data in column B, but every row after the first CA instance I am getting a #REF! error. I copied and pasted the values of the reference cells for each matrix so the data should all be the same and I didn't change it after the formula was created. Wish I could send the file to someone to look at it but I just cannot seem to get it to work for all of the data.

Please help.

[#2]

@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).

[#3]

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 *.

[#4]

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))

[#5]

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.

[#6]

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))