INDEX MATCH Lookup

[andrewc]

Hi,

I have sample data in columns A to H from which I’d like, ideally, to use the INDEX-MATCH combination to extract the results shown in columns J:L.

The text in column B indicates which columns should be referenced in the lookup. For example, BBB has the word “Food” in column B so I want to use the text “Food 1d”, “Food 1w” and “Food 1y” in the formula to return the results in cells J14:L14. Similarly, for CCC I’d like to return the data in the “Colour 1d”, “Colour 1w” and “Colour 1y” columns.

Can someone please suggest a solution.

Thanks!

[AlKey]

Enter this formula in J4 and pull it across and then down

=SUMIFS(INDEX($C$4:$H$7,0,MATCH($B4&" "&J$3,$C$3:$H$3,0)),$A$4:$A$7,$A4)

[andrewc]

That works a treat, thank you!

[AlKey]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).