May 19 2022 12:40 PM
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.
May 19 2022 12:59 PM
@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).
May 19 2022 05:00 PM
May 19 2022 06:17 PM
May 20 2022 04:55 AM
May 20 2022 05:15 AM