1

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

2 Answers 2

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

Multi-Column Example




Functions Used

COUNTAFILTERINDEXLAMBDALETMAPMATCHSORT




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)}))))
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.