Result lists for multiple conditions

[Coach_KM]

I am having a hard time figuring out the nested formulas to solve this. In short I would like a worksheet with a list of all events a participant is in.

I have a worksheet that has all of the participants names in a single column and I would like to add a column that returns the event name from column C , for every event that person is in. There may be multiple events for each person, those events are marked with an "a", "b", "c", "d". That list could be seperate cells or a single, doesn't really matter. The names in row 1 are dynamic and are sorted by another worksheet so the formula would need to be flexible.

I understand index and match and lookup functions pretty well, but I just can't figure out the way to put them all together.

example:
Aireth L : Womens AA-A 8+ / Womens D 8+
Alex N : Mens C 1x

NW Regionals 2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	2024 NW Masters Regionals				Aireth L	Alex N	Carly M	Charles S	Charlotte C	Claudia L	Kelley W
2					3	3	2	2	1	1	3
3			Saturday		2	1	2	1	1	1	2
4			Sunday		1	2	0	1	0	0	1
5	#		Event
6
7			SATURDAY
8	44		Womens AA-A 2x
9	49		Mens C 4+
10	50		Womens C 4x				B
11	56		Mens B 2x
12	57		Womens AA-A 8+		A				A	A
13	58		Mens D 8+
14	59		Womens D 2x								B
15	61		Mixed AA 2x
16	62		Womens B 4+
17	68		Mixed B 8+
18	69		Mens C 1x			A
19	71		Mens AA-A 4+
20	73		Womens C 2-
21	76		Womens AA-A 4x
22	79		Mixed D 8+
23	80		Mixed C 8+
24	83		Mens D 2x					B
25	85		Womens D 8+		B
26	86		Mens AA-B 8+
27	88		Womens B 2x				B
28	89		Mixed C 4+
29	91		Mens AA-B 2-
30	92		Womens C 1x
31	94		Mens A 1x
32	95		Womens AA-A 2-
33	96		Mens D 4+
34	97		Womens D 4x								B
35	98		Mixed B 2x
36	99		Mixed AA-A 4+
37	100		Womens C 8+
38			SUNDAY
Race schedule

Cell Formulas
Range	Formula
E2:K2	E2	=SUM(E3:E4)
E3:K3	E3	=COUNTIF(E$8:E$37,"a")+COUNTIF(E$8:E$37,"b")+COUNTIF(E$8:E$37,"c")+COUNTIF(E$8:E$37,"d")
E4:K4	E4	=COUNTIF(E$39:E$79,"a")+COUNTIF(E$39:E$79,"b")+COUNTIF(E$39:E$79,"c")+COUNTIF(E$39:E$79,"d")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:K4	Cell Value	="no"	text	YES
E8:K37	Expression	=E$3="NO"	text	NO
E7:K79	Cell Value	="A"	text	NO
E7:K79	Cell Value	="B"	text	NO
E7:K79	Cell Value	="C"	text	NO
E7:K79	Cell Value	="D"	text	NO
E7:K79	Cell Value	="E"	text	NO
E7:K79	Cell Value	="F"	text	NO
E2:K4	Cell Value	=1	text	NO
E2:K4	Cell Value	=2	text	NO
E2:K2	Cell Value	=3	text	NO
E2:K2	Cell Value	=4	text	NO
E3:K4	Cell Value	=3	text	NO
E3:K4	Cell Value	>3	text	NO
E7:K79	Cell Value	contains "(c)"	text	NO
E4:K4	Expression	=AND(E4="no",COUNTIF(E39:E79,"<>""")-COUNTIF(E39:E79,"")>0)	text	NO
E2:K2	Cell Value	>4	text	NO
E3:K3	Expression	=AND(E3="no",COUNTIF(E8:E37,"<>""")-COUNTIF(E8:E37,"")>0)	text	NO

 

[Cubist]

See cell M8. Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	2024 NW Masters Regionals				Aireth L	Alex N	Carly M	Charles S	Charlotte C	Claudia L	Kelley W
2					2	1	2	1	1	1	2
3			Saturday		2	1	2	1	1	1	2
4			Sunday		0	0	0	0	0	0	0
5	#		Event
6
7			SATURDAY
8	44		Womens AA-A 2x										Aireth L: Womens AA-A 8+, Womens D 8+
9	49		Mens C 4+										Alex N: Mens C 1x
10	50		Womens C 4x				B						Carly M: Womens C 4x, Womens B 2x
11	56		Mens B 2x										Charles S: Mens D 2x
12	57		Womens AA-A 8+		A				A	A			Charlotte C: Womens AA-A 8+
13	58		Mens D 8+										Claudia L: Womens AA-A 8+
14	59		Womens D 2x								B		Kelley W: Womens D 2x, Womens D 4x
15	61		Mixed AA 2x
Sheet4

Cell Formulas
Range	Formula
E2:K2	E2	=SUM(E3:E4)
E3:K3	E3	=COUNTIF(E$8:E$37,"a")+COUNTIF(E$8:E$37,"b")+COUNTIF(E$8:E$37,"c")+COUNTIF(E$8:E$37,"d")
E4:K4	E4	=COUNTIF(E$39:E$79,"a")+COUNTIF(E$39:E$79,"b")+COUNTIF(E$39:E$79,"c")+COUNTIF(E$39:E$79,"d")
M8:M14	M8	=LET(names,TOCOL(E1:K1),BYROW(names,LAMBDA(name,name & ": " & TEXTJOIN(", ",TRUE,IF(FILTER(E8:K37,E1:K1=name)<>"",C8:C37,"")))))
Dynamic array formulas.

 

[Cubist]

Didn't really need the LET() above.

=BYROW(TOCOL(E1:K1),LAMBDA(name,name & ": " & TEXTJOIN(", ",TRUE,IF(FILTER(E8:K37,E1:K1=name)<>"",C8:C37,""))))

 

[Coach_KM]

That is amazing @Cubist . Thank you!

I forgot to mention that all of the values in the grid E8:K37 are formulas that result in either a "" if not true or "a,b,c,d" if true. I simplified the mini sheet because the data for that grid comes from another worksheet. So I'm only getting the names result, but I understand why now.

If it helps the formula is:
=IFERROR(IF(COUNTIF(INDIRECT($I10&"a"),AL$2),"A",IF(COUNTIF(INDIRECT($I10&"B"),AL$2),"B",IF(COUNTIF(INDIRECT($I10&"C"),AL$2),"C",IF(COUNTIF(INDIRECT($I10&"D"),AL$2),"D","")))),"")
There are columns missing from the mini sheet that work to break out the event name, so this formula doesn't quite jive with what's posted.

 

[Cubist]

I forgot to mention that all of the values in the grid E8:K37 are formulas that result in either a "" if not true or "a,b,c,d" if true. I simplified the mini sheet because the data for that grid comes from another worksheet. So I'm only getting the names result, but I understand why now.

The "" results from formulas should've still work.

There are columns missing from the mini sheet that work to break out the event name, so this formula doesn't quite jive with what's posted.

Can you post what the actual sheet look like?

 

[Coach_KM]

I sorted it out. I made a mistake when changing the ranges to the actual data. It's working beautifully.
Thank you again.

 

[Cubist]

Glad you figured it out. Cheers.