Excel Search query cell for multiple values and return those search values from a different table, and return column headers

Question

I'm helping a friend's son with an excel exercise and it has me stumped. I'm not even sure if excel can handle something like this.

We have a column that has multiple cities, and multiple cities within a couple of cells, and we need to find out what sports teams are in that city from a different sheet, and if those cities are affiliated with the various leagues.

Sheet to input cities:

Sheet to lookup which teams are in that city:

Obviously these aren't all the teams in the regions, so the goal is to eventually add cities to column A on sheet 1 and teams on sheet 2 so that they would update automatically. The end product should look something like this:

I'm struggling with the multiple cities in column A, otherwise this would be a simple Vlookup exercise with concatenation. The real curveball (Hah, I know) here is that Frisco has two teams from Dallas as well and we don't want duplicates.

Any ideas on how to solve this?

We know this has something to do with TEXTJOIN, IF, SEARCH, ISNUMBER, and possible FIND, but the duplicates are what's really throwing (Hah) us off. I'm sure it's a simple answer, but our brains haven't quite figured out the exact syntax of the statements.

P.b · Answer 1 · 2023-06-28T06:29:55.053

My advise would be to add a column Region to both tables, so you can easily reference them, but since that's not the question, here's a solution:

If your data is on a sheet called Data: Data

	A	B	C	D	E	F	G
1	City	NFL	NBA	MLB	MLS	NHL	MiLB
2	Houston	Texans	Rockets	Astros	Dynamo
3	Dallas	Cowboys	Mavericks	Rangers	FC Dallas	Stars
4	Austin				Austin FC
5	San Antonio		Spurs				Missions
6	Sugar Land						Space Cowboys
7	Plano
8	Frisco	Cowboys				Stars

And the table where you want your result containing the following data: Result

	A	B	C	D	E	F	G	H
1	City	Teams in Region	NFL	NBA	MLB	MLS	NHL	MiLB
2	Sugar Land, Houston
3	Austin
4	Dallas, Plano, Frisco
5	San Antonio

Then on the Result-sheet in B2 you could use the following: =SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(MAKEARRAY(ROWS(A2:A5),1,LAMBDA(r,c,ARRAYTOTEXT(REPT(SUBSTITUTE(Data!B2:G8," ","|"),MATCH("*"&Data!A2:A8&"*",A2:A5,0)=r)))),", "," "))," ",", "),"|"," ")

Note that this solution includes duplicates. If you prefer unique values, you could use:

   =MAP(A2:A5,
 LAMBDA(x,
       LET(a,REPT(Data!B2:G8,
                  IF(MMULT(--(Data!A2:A8=TEXTSPLIT(x,", ")),
                           SEQUENCE(COLUMNS(TEXTSPLIT(x,", ")))),
                     1,
                     NA())),
ARRAYTOTEXT(UNIQUE(TOCOL(IF(a="",NA(),a),3))))))

And for the TRUE / FALSE values in C2 you could use:

=IF(DROP(
         REDUCE(0, A2:A5,
         LAMBDA(x, y,
                VSTACK(x,
                       REDUCE(0, TEXTSPLIT(y,,", "),
                       LAMBDA(a, b,
                              a + IF(
                                     INDEX(Data!B2:G8,XMATCH(b,Data!A2:A8),)<>"",
                                     1,
                                     0)))))),
         1),
    "Yes","No")

Excel Search query cell for multiple values and return those search values from a different table, and return column headers

1 Answers1