Maybe I'm asking a pretty basic question, but I've searched here (and on other sites) for an answer and I couldn't find it.
I'm making a work schedule for my workplace on Google Sheets, analogous to the example posted as an image down here, that includes an automatic time card for every worker (that is, a formula answering the question "which days had [WORKER] worked?").
I managed to make one, with the filter formula
=FILTER(A2:A16;{or(COUNTIF(B2:D2;$L$1);COUNTIF(F2:H2;$L$1));or(COUNTIF(B3:D3;$L$1);COUNTIF(F3:H3;$L$1));or(COUNTIF(B4:D4;$L$1);COUNTIF(F4:H4;$L$1));or(COUNTIF(B5:D5;$L$1);COUNTIF(F5:H5;$L$1));or(COUNTIF(B6:D6;$L$1);COUNTIF(F6:H6;$L$1));or(COUNTIF(B7:D7;$L$1);COUNTIF(F7:H7;$L$1));or(COUNTIF(B8:D8;$L$1);COUNTIF(F8:H8;$L$1));or(COUNTIF(B9:D9;$L$1);COUNTIF(F9:H9;$L$1));or(COUNTIF(B10:D10;$L$1);COUNTIF(F10:H10;$L$1));or(COUNTIF(B11:D11;$L$1);COUNTIF(F11:H11;$L$1));or(COUNTIF(B12:D12;$L$1);COUNTIF(F12:H12;$L$1));or(COUNTIF(B13:D13;$L$1);COUNTIF(F13:H13;$L$1));or(COUNTIF(B14:D14;$L$1);COUNTIF(F14:H14;$L$1));or(COUNTIF(B15:D15;$L$1);COUNTIF(F15:H15;$L$1));or(COUNTIF(B16:D16;$L$1);COUNTIF(F16:H16;$L$1))}=TRUE)
that is the day column A2:A16 filtered with a manual array column that check if every row B:D (representing an AM shift) OR every row F:H (PM shift) contains the worker's name $L$1.
This is not an optimal solution, because I have to write the array manually, in a very time-consuming and not flexible way (to add another column/another worker to the workshifts means that I'll have to correct every single row).
Is there another better way to use FILTER? So far I've been trying to use MATCH or HLOOKUP, but I'm a rookie and not so familiar with excel arrays.
