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

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

#### 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)}))))
``````

## 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)}))))
``````

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)
``````