Filter table rows for partial string and return column headers as dynamic array

[tschatz]

Hello,

Given the table (Table3) representing the 1:1 slots between 2 participants with the following data:

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U
2	TIMESLOT	Group 1	Group 2	Group 3	Group 4	Group 5	Group 6			Participant	Slots to participate	Group
3	09h00-09h15	X8_1A - X8_B	X8_1E - X8_2B	X8_1B - X8_5	X8_1C - X8_3	X8_1D - X8_A	X8_2A - X8_6			X8_1A	09h00-09h15	Group 1
4	09h15-09h30	X8_1A - X8_4	X8_1C - X8_2A	X8_6 - X8_3	X8_2C - X8_A	X8_2B - X8_B	X8_1D - X8_5				09h15-09h30	Group 1
5	09h30-09h45	X8_2B - X8_6	X8_1D - X8_3	X8_2A - X8_5	X8_1F - X8_2C	X8_A - X8_B	X8_1E - X8_4				10h30-10h45	Group 1
6	09h45-10h00	X8_1D - X8_6	X8_A - X8_4	X8_1C - X8_B	X8_2A - X8_3	X8_1F - X8_5	X8_1B - X8_2B				10h45-11h00	Group 5
7	10h00-10h15	X8_1C - X8_6	X8_1D - X8_2B	X8_1B - X8_A	X8_2C - X8_4	X8_1F - X8_3	X8_5 - X8_B				11h00-11h15	Group 3
8	10h15-10h30	X8_6 - X8_4	X8_2B - X8_A	X8_2C - X8_B	X8_1B - X8_3	X8_1D - X8_2A	X8_1C - X8_5				11h15-11h30	Group 6
9	10h30-10h45	X8_1A - X8_6	X8_2B - X8_4	X8_2A - X8_A	X8_1F - X8_B	X8_1E - X8_3	X8_1C - X8_2C				11h30-11h45	Group 6
10	10h45-11h00	X8_1F - X8_6	X8_1D - X8_4	X8_1C - X8_2B	X8_1E - X8_A	X8_1A - X8_3	X8_2C - X8_5				12h00-12h15	Group 5
11	11h00-11h15	X8_6 - X8_B	X8_5 - X8_4	X8_1A - X8_A	X8_2B - X8_3	X8_1B - X8_2C	X8_1E - X8_2A				12h15-12h30	Group 4
12	11h15-11h30	X8_6 - X8_A	X8_3 - X8_B	X8_1C - X8_4	X8_1E - X8_5	X8_1F - X8_2B	X8_1A - X8_2A
13	11h30-11h45	X8_1E - X8_6	X8_5 - X8_3	X8_1F - X8_A	X8_1B - X8_B	X8_2A - X8_4	X8_1A - X8_2C
14	11h45-12h00	X8_4 - X8_3	X8_1E - X8_B	X8_2B - X8_5	X8_2C - X8_6	X8_1C - X8_A	X8_1B - X8_2A
15	12h00-12h15	X8_2C - X8_3	X8_1B - X8_4	X8_1D - X8_B	X8_6 - X8_5	X8_1A - X8_2B	X8_1F - X8_2A
16	12h15-12h30	X8_1F - X8_4	X8_1D - X8_2C	X8_A - X8_3	X8_1A - X8_5	X8_2A - X8_B	X8_1B - X8_6
17	12h30-12h45	X8_1E - X8_2C	X8_4 - X8_B	X8_5 - X8_A
INPUT Overall

Cell Formulas
Range	Formula
T3:T11	T3	=FILTER(Table3[TIMESLOT],MMULT(--(ISNUMBER(SEARCH($S$3,Table3[[Group 1]:[Group 6]]))),SEQUENCE(COLUMNS(Table3[@[Group 1]:[Group 6]]),,,0))>0)
Dynamic array formulas.

I would like to dynamically query (via Cell S3) the Timeslots and respective Groups for the defined participant.
Thanks to Fluff, the timeslot query is already working (see this thread).

Could anyone please point me in the right direction on how to query the groups --> Dynamically calculate Column "U"?

Thanks in advance.

 

[Fluff]

How about

=LET(m,MMULT(--(ISNUMBER(SEARCH(J2,Table3[[Group 1]:[Group 6]]))),SEQUENCE(COLUMNS(Table3[[#Headers],[Group 1]:[Group 6]]))),CHOOSE({1,2},FILTER(Table3[TIMESLOT],m>0),INDEX(Table3[[#Headers],[Group 1]:[Group 6]],FILTER(m,m>0))))

 

[tschatz]

Works like a charm, thanks a lot

 

[Fluff]

You're welcome & thanks for the feedback.