Index / Match ?? Not sure.....

[WGarrison]

This is probably a very simple request, but I am stumped. Hopefully someone can lend a hand.

On a main data sheet, I have a populated row of data (header). Below the header, I have certain cells populated with data. I want to do a look up on a separate sheet and match the 'header' and return the populated data below it. Is this possible?

Ex:

AAA	BBB	CCC	DDD	EEE	FFF
Blue		Silk		Black
	Hot	Stinky	Cold		Warm

<tbody>
</tbody>

Results I am looking for:
AAA = Blue
BBB = Hot
CCC = Silk, Stinky

 

[Peter_SSs]

Results I am looking for:
AAA = Blue
BBB = Hot
CCC = Silk, Stinky

Should those blue results be in a single cell or two cells?
If two cells would those two cells be across a row or above/below each other in a column?

 

[WGarrison]

Those blue results should be in separate cells. Preferably a column. One below the other.

 

[Peter_SSs]

Those blue results should be in separate cells. Preferably a column. One below the other.

OK, see if this works for you.

Excel 2016
	A	B	C	D	E	F
1	AAA	BBB	CCC	DDD	EEE	FFF
2	Blue		Silk		Black
3		Hot	Stinky	Cold		Warm
4
Data

Formula in A2 is copied across and down

Excel 2016
	A	B	C	D	E	F
1	AAA	BBB	CCC	DDD	EEE	FFF
2	Blue	Hot	Silk	Cold	Black	Warm
3			Stinky
4
5
Summary

Cell Formulas
Range	Formula
A2	=IFERROR(INDEX(Data!A$2:A$100,AGGREGATE(15,6,ROW(Data!A$2:A$100)-ROW(Data!A$2)+1/(Data!A$2:A$100<>""),ROWS(A$2:A2))),"")