Extract data from one table to another

[scruffy]

Hi Folks. I am trying to extract data from a table arranged as show immediately below, and have it show up in the second table, which is organized differently. The team is the Y axis and the X axis is the month

team March April May
red ohio
white texas
purple maine oklahoma
green ohio idaho oklahoma

I would like the data to show up as below with now the state on the X axis and the actual color in the body of the table. Is there a formula that can populate the colors into the new table layout below that pulls from the table above? For example, I'd like to find those teams going to ohio, in the top table, and have them listed table below as indicated. would like to do so as a formula if possible.

ohio texas oklahoma maine idaho
Color 1 red white pruple purple green
Color 2 green green

Thank you very much.

 

[Anthony47]

You can attach Images or, better, XL2BB minishets to make the request clearer
Also, which is your Office version?

 

[scruffy]

I am following up on my previous thread that I messed up (twice now) and have included a image to help in the process. If I get flagged again, I won't repost. I don't want to waste peoples time.

I am trying to create a formula bottom table in the shaded area that organizes the data as I've inputted manually, that links to the data from the top table. The x/y axis are different and the data is organized slightly differently. My preference would be to do this as a formula if possible.

 

[Anthony47]

Also, which is your Office version?

I will assume you work with Office 365

In this case, with reference to the XL2BB minisheet shown below, I created the output table using in H5 the formula

=LET(GreyArea,$B$4:$E$7,Teams,$A$4:$A$7,Tarr,TEXTSPLIT(TEXTJOIN("/",TRUE,REPT(INDEX(Teams,SEQUENCE(ROWS(Teams)))&"/",COLUMNS(GreyArea))),,"/",TRUE),Area,TEXTSPLIT(TEXTJOIN("/",FALSE,GreyArea),,"/"),FILTER(Tarr,Area=H3))

Copy then to the right for as many headers you have in Row 3

byWnG_C30316_Elenco capitoli numerato.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		May	June	July	Aug
3	Team							Alaska	Ohio	Hawaii	Nevada	Indiana	Maine
4	Sharks	Alaska	Nevada	Indiana			# of teams
5	Tigers	Ohio	Nevada				1	Sharks	Tigers	Pandas	Sharks	Sharks	Bears
6	Bears	Alaska	Nevada		Maine		2	Bears			Tigers		Pandas
7	Pandas	Hawaii	Nevada		Maine		3				Bears
8							4				Pandas
9
Foglio2

Cell Formulas
Range	Formula
H5:H6,M5:M6,K5:K8,I5:J5,L5	H5	=LET(GreyArea,$B$4:$E$7,Teams,$A$4:$A$7,Tarr,TEXTSPLIT(TEXTJOIN("/",TRUE,REPT(INDEX(Teams,SEQUENCE(ROWS(Teams)))&"/",COLUMNS(GreyArea))),,"/",TRUE),Area,TEXTSPLIT(TEXTJOIN("/",FALSE,GreyArea),,"/"),FILTER(Tarr,Area=H3))
Dynamic array formulas.

 

[scruffy]

This is spectacular Anthony. Thank you very much. I appreciate your time investigating this for me.
-G