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