Match & Index Formula W/ Row Conditions

[JPRICH16]

Hi!

I actually learned how to Index + Match on this forum funny enough.

Basically I have a data set with seperate data but with similiar headings (i.e coloumns and rows).

I started doing Index/Match formulas and just making each formula cover a limited range to ensure no crossover.

I was wondering though if a condition can be put into the formula!

i.e right now its =INDEX(DATA!A9:N16,MATCH('END PRODUCT'!A4,DATA!A9:A16,0),MATCH('END PRODUCT'!D1,DATA!A9:N9,0))

but it should be =INDEX(DATA!A2:N36) except that the matching will see Q1 or VP multiple times so I somehow need to add if the conditions in A9, A18, A26, A32 are the same value as the first page so this formula doesnt grab the first match it sees!

IN SUMMARY - the first green cell on the second tab should be 2 and the second one 3 but having the same formula so i can copy and paste it

Please see attached!

Jeffrey

[Glenn Kennedy]

Not a nice data layout. All those separate, disjointed unequally-sized tables.....


=INDEX(DATA!F:F,AGGREGATE(15,6,ROW(DATA!$F$10:$F$50)/(DATA!$A$10:$A$50=$A2),COUNTIF($A$2:$A2,$A2)))

copied across and down.

[JPRICH16]

You're the GOAT - ill watch some youtube videos on that formula it would be a complete life saver for other stuff! THANK YOU!