How to filter a schedule to return a list of employees based on date and shift code?

Question

In Google Sheets, I have an employee work shift scheduling sheet named Shifts2.

• Column A lists all employees.
• Row 1 is a list of dates in November.
• The remaining rows in columns B through AG contain shift codes.

On any given date, scheduled employees have a shift code entered in their row.

• Multiple employees work each shift
• Employees who share a shift also share the same code.

Sample Data: Shifts2

	A	B	C
1	Names	01/11/2023	02/11/2023
2	Landon Jameson	DO	DO
3	Avery Rose	17-01	17-01
4	Makayla Grace	22-06	22-06
5	Kamryn Elizabeth	09-17	09-17
6	Jovanny Alexander	17-01	17-01
7	Lila Marie	DO	DO
8	Kamila Nicole	09-17	09-17
9	Jovany Michael	DO	06-14
10	Liliana Faith	17-01	17-01

In the sheet Selections, I want a formula that returns a list of employee names based on a specified date and shift code.

• A2 contains the lookup date
• C2 contains a shift code
• C7 contains the formula.

How can I accomplish this? I also intend to reuse the formula in D7 using the same date but with a different shift (specified in D2).

Sample Results: Selections

	A	B	C	D
1	Select a Date		1st Shift	2nd Shift
2	01/11/2023		09-17	17-01
3
4
5
6			Agents on 1st Shift	Agents on 2nd Shift
7			Kamryn Elizabeth	Avery Rose
8			Kamila Nicole	Jovanny Alexander
9				Liliana Faith

Link to my Google spreadsheet

Answer 1

Important Note: Formula Localization

@maidenas' locale uses , as the decimal separator ( ½=0,5 ) and the formula syntax is slightly different for separating arguments and array elements than locales that use . as the decimal separator ( ½=0.5 ) such as North America (NA). NA versions of the formulas are included at the bottom of the post.

---

Formula

FILTER is often combined with INDEX and MATCH in similar situations. I have also sorted the results by name. SORT can be removed if unwanted.

The first 2 formulas are equivalent:

=SORT(FILTER(Shifts2!A1:A100; 
   INDEX(Shifts2!1:100;;
     MATCH(A2;Shifts2!1:1;0))=C2))

=LET(data;Shifts2!1:100;
   SORT(FILTER(INDEX(data;;1); 
     INDEX(data;;MATCH(
       A2;INDEX(data;1);0))=C2)))  

To perform the calculation for both shifts (or even more) using a single formula in C7 use the following formula. Note it also includes a staff count above the names.

=LET(data;Shifts2!1:100;
   MAP(C2:D2; LAMBDA(shift;
     LET(staff; FILTER(INDEX(data;;1); 
       INDEX(data;;MATCH(A2;INDEX(data;1);0))=shift);
         {"Staff Count: "&COUNTA(staff);SORT(staff)}))))

---

Functions Used

COUNTA FILTER INDEX LAMBDA LET MAP MATCH SORT

---

North American Syntax

=SORT(FILTER(Shifts2!A1:A100, 
   INDEX(Shifts2!1:100,,
     MATCH(A2,Shifts2!1:1,0))=C2))

=LET(data,Shifts2!1:100,
   SORT(FILTER(INDEX(data,,1), 
     INDEX(data,,MATCH(
       A2,INDEX(data,1),0))=C2))) 

=LET(data,Shifts2!1:100,
   MAP(C2:D2, LAMBDA(shift,
    LET(staff, FILTER(INDEX(data,,1), 
     INDEX(data,,MATCH(A2,INDEX(data,1),0))=shift),
       {"Staff Count: "&COUNTA(staff);SORT(staff)}))))

Answer 2

I eventually found the solution.

I just used a filter inside a filter. Firstly I filtered all the employees who worked the date I chose, and then I filtered the results based on the shift code.

Formula in C7:

=FILTER(Shifts2!A2:A52;FILTER(Shifts2!B2:AG52;Shifts2!B1:AE1=A2)=C2)

Formula in D7:

=FILTER(Shifts2!A2:A52;FILTER(Shifts2!B2:AG52;Shifts2!B1:AE1=A2)=D2)