Having trouble printing names from one column into another table's rows If a condition is met

[RBoyerr]

PC/Windows 10 Pro

Excel Version 2207 (Build 15427.20210)

Sample File 

Note: this workbook was copied from the original which is linked to a Form via Power Automate

 I've tried Vlookup and Index and Match, but cant figure out how to "print" respondents' names from Table1 Column D into cells D30:I68 in Table2 IF the respondent signed up for a particular day.

For Example: I want the names of everyone who signed up for 25-Nov to show up in cells in Row 30.

Additional Info:

People can sign up for anywhere from 1-38 days, so Day(s) in Row 2 don't equal the first day of the sign-up sheet (25-Nov).

When a Form response is submitted, every date that is selected by the respondent prints in Column E in Table1. Currently I am using Text to Columns to give each date its own cell 

[#2 · Accepted solution]

@RBoyerr 

See the attached version. I corrected the Jan-1 and Jan-2 dates to be in 2023 instead of 2022.

[#3]

Hi@RBoyerr,  

"I want the names of everyone who signed up for 25-Nov to show up in cells in Row 30."

=TRANSPOSE(FILTER(Table1[Name],ISNUMBER(BYROW(Table1[[Day(s)1]:[ Day(s)38]],LAMBDA(array,XMATCH(C30,array))))))

I did not understand what you wanted in addition.

[#4]

@Hans Vogelaar This is amazing! Thank you.

It looks like you achieved this through a combination of TRANSPOSE and MMULT functions. Just so I can understand how you achieved this, could you explain in simple terms how your formula achieved my desired results, if not that's also cool-you've given me new areas to explore 

[#5]

@RBoyerr 

I'll try to get back to you later, no time now.

[#6]

@RBoyerr 

Table1[[Day(s)1]:[ Day(s)38]]=C30 returns an array of TRUE/FALSE values: TRUE for each cell in Table1[[Day(s)1]:[ Day(s)38]] that contains the date in C30 (25-Nov), FALSE otherwise.

--(Table1[[Day(s)1]:[ Day(s)38]]=C30) converts TRUE to 1 and FALSE to 0:

Each row corresponds to a student. We'd like to know which rows contain a 1.

COLUMN(Table1[[Day(s)1]:[ Day(s)38]]) returns the column numbers of the Table1[[Day(s)1]:[ Day(s)38]])range:

COLUMN(Table1[[Day(s)1]:[ Day(s)38]])^0 raises the column numbers to the power of 0 resulting in all 1s:

Transposing results in a column with as many 1s as days1 -- days 38 has columns (i.e. 38):

MMULT multiplies the array of 0s and 1s with this column. The result is a 1 for each row of the array that contains (at least) a 1, i.e. the student has subscribed to that date.

This is used to filter the name column, resulting in a list of names for the date:

Finally, TRANSPOSE converts this column to a row.

[#7]

Thank you! This is a great breakdown, and really helped me understand the functions. Thanks again