Filter function or alternative for Vertical and Horizontal Criteria

Question

[enter image description here][1]I would like to pull some data from a table which has lists of names, times, supervisors and weekly schedule (7 days) for each individual employee. (they are all columns)

The table which I need to import the data to has just the date criteria so I need to have one monthly column and easy day with the relevant names, times, supervisors and (the day, they work)

This functions works well to pull the data but I find it pretty annoying in terms of simple modifications needs.

The input table has headers ( name list, time, supervisor, Saturday, Sunday,,,,, etc) The input method used for the schedule is a data validation list method either Yes or No Also, IFERROR limits the ability of recognizing any other sort of error if the function didn't work for any reason

Here is my try

=IFERROR(IFS(TEXT(A2,"dddd")="Saturday",FILTER(Schedule!$A$2:$C$61,Schedule!$E$2:$E$61="yes"),TEXT(A2,"dddd")="Sunday",FILTER(Schedule!$A$2:$C$61,Schedule!$F$2:$F$61="yes"),TEXT(A2,"dddd")="Monday",FILTER(Schedule!$A$2:$C$61,Schedule!$G$2:$G$61="yes"),TEXT(A2,"dddd")="Tuesday",FILTER(Schedule!$A$2:$C$61,Schedule!$H$2:$H$61="yes"),TEXT(A2,"dddd")="Wednesday",FILTER(Schedule!$A$2:$C$61,Schedule!$I$2:$I$61="yes"),TEXT(A2,"dddd")="Thursday",FILTER(Schedule!$A$2:$C$61,Schedule!$J$2:$J$61="yes"),TEXT(A2,"dddd")="Friday",FILTER(Schedule!$A$2:$C$61,Schedule!$K$2:$K$61="yes")),"none")

I am looking for ideas to make it simpler and sorter with the same results

Thank you in advance!

IF, IFS, FILTER, XLOOKUP

enter image description here

Answer 1

You just need nested FILTER() function. Try-

=FILTER(A2:C20,FILTER(E2:K20,E1:K1=TEXT(M2,"dddd"))="Yes")