I have a very large table looking somewhat along the lines of the below example:

        Date1   Date2   Date3   Date4   Date5
        ShiftA  ShiftB  ShiftA  ShiftB  ShiftA
Person1 x       x       x       x       late
Person2 late    x       late    late    x
Person3 late    late    x       x       x

I'd like to find some way of displaying a list of the dates on which an individual was "late" to a certain shift type.

Using two input values, for example "Person2" and "ShiftA", it would yield an output "Date1", "Date3" (optimally the output would be in the form of a row with one cell for each date, but that's not necessarily within the scope of this question).

I've tried using things like nested filter() functions, query() functions and index() functions containing match(), but I keep running into different issues and I feel like I'm not quite savvy enough at Google Sheets to find a great solution to my problem.

Any push in the right direction would also be greatly appreciated!

1 Answer 1


The shape of your data makes harder to get the result that you are looking for. The first step in the "right direction", is to transform the data into a simple table structure like the following:

|   Person    |   Date   |   Shift  |   Value    |
|   Person 1  |   Date1  |  ShiftA  |     X      |
|   Person 1  |   Date1  |  ShiftB  |   late     |

Once your data is transformed into the above shape it will be more easy to get

a list of the dates on which an individual was "late" to a certain shift type

You could use FILTER, QUERY, a Pivot Table, etc.

Related questions in this site

Related question in Stack Overflow

Other related in Stack Overflow


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.