Forum Discussion

JoeEason92's avatar
JoeEason92
Copper Contributor
Jun 30, 2023

Arrays

I have a formula which matches a date on a roster to check if someone is on shift and display their name on another sheet. Cell B2 displays =TODAY() so it should automatically count up the roster, the idea is to have a live display of who is on shift. The roster is based on colour so I have a VBA script which assigns a value to different colours. In this case a value of 3 is a day shift. So this formula matches the date in cell B2 with a date from row 3 on sheet1 and checks the corresponding cell in row 9 for a value of 3, if this is true it displays the corresponding cell of column D.

 

=IF(AND(COUNTIF(Sheet1!E3:NN3,B2)>0, INDEX(Sheet1!E9:NN9, MATCH(B2, Sheet1!E3:NN3, 0))=3), Sheet1!D9, "")

 

This is fine for saying 'is "x" on shift, yes or no' and i can use this for the single supervisor on shift. But I need to either edit this formula or use a different array in order to do the same thing for multiple employees. I need to match cell B2 with a cell from row 3 and then find a corresponding cell from the same column from rows 10-17 with a value of 3 and display the corresponding cell from column D. I've tried a couple of arrays e.g

 

=IF(INDEX(Sheet1!10:17, MATCH(B2, Sheet1!E3:NN3, 0), COLUMN($A$1))=3, INDEX(D10:17, MATCH(B2, Sheet1!E3:NN3,0)), "")

 

but I can't seem to get anything to work. Any help would be greatly appreciated.

Excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 30, 2023

    JoeEason92 

    To display the names of multiple employees who are on shift based on your criteria, you can modify the formula using array formulas.

    Here is a suggested solution:

    =IFERROR(INDEX(Sheet1!$D$10:$D$17, SMALL(IF((Sheet1!$E$9:$NN$9=3)*(Sheet1!$E$10:$NN$17<>""), ROW(Sheet1!$E$10:$NN$17)-ROW(Sheet1!$E$10)+1), ROW(A1))), "")

    This formula should be entered as an array formula by pressing Ctrl+Shift+Enter. Then you can drag it down to populate the names of multiple employees who are on shift.

    Here's how the formula works:

    1. The IF function checks two conditions: whether the value in the corresponding cell of row 9 equals 3 and whether the corresponding cell of rows 10-17 is not empty.
    2. If both conditions are met, the ROW function returns the row numbers of the valid cells, adjusted relative to the starting row (row 10 in this case).
    3. The SMALL function is used to retrieve the k-th smallest row number from the array generated by the IF function. The ROW(A1) part increases the k value for each row, allowing the formula to populate the names for multiple employees.
    4. The INDEX function retrieves the names from column D based on the row numbers obtained in step 3.
    5. If there are no more valid names to display, the formula returns an empty string ("").
    6. The IFERROR function is used to handle any potential errors and display an empty string instead.

    Make sure to adjust the ranges (Sheet1!$D$10:$D$17, Sheet1!$E$9:$NN$9, and Sheet1!$E$10:$NN$17) according to your actual data layout.

    By using this formula, you should be able to display the names of multiple employees who have a value of 3 in the corresponding cell of rows 10-17 when matching with the date in cell B2.

    The texts, steps and functions were created with the help of AI for reasons of time.

     

    Hope this will help you.

Resources