Is there a calc formula (or some other way) that searches a table for multiple conditions?

[BillEnnals]

I have a table with five columns that contains data for an Employee shift schedule.

Is there a formula, script, or other method of searching that table and returning values based on the following logic (apologies if this description is a bit rough).

IF ‘Shift’ = ‘Sales-AM’ AND DATE = a Monday between 2019-07-01 and 2019-12-31 then return that (or those) Employee’s name(s).

My aim is to have a 2d matrix with the ‘Shift’ name in the x-axis, the Dates in the y-axis and the cells within the matrix to be filled with the correct Employee names based on the x and y values, like the following table:

If feels like this should not be too hard to accomplish but I’m pretty new to spreadsheets and I haven’t found any examples elsewhere that are trying to accomplish this kind of thing.

Thanks for reading,

Bill.

[gregors15]

Hi,
I have created a pivot table using your data, plus 2 additional columns, one for an employee number and one concatenating the employee name and the number.

The layout to produce the pivot table is ,
The row fields, “Date from” and the “concatenated name and number”.
The column fields, “Shift”
Data fields is the “employee number”.
I don’t know of anyway to get the name to appear as data. The “Total Results” fields are not used.

I know its not exactly as you requested, let us know if it helps.

LOQ_20190629.ods

[BillEnnals]

Thanks for answering @gregors15. It’s a useful suggestion to use an employee code in a pivot table.

[SM_Riga]

Hello @BillEnnals

I have created sample spreadsheet for you, demonstrating how such timetable can be filled with array formulas.

Tip 1. Boolean True and False values are the same as 1 and 0 integers in arithmetic operations. This allows you to run True/False test with multiple conditions as the product of these test conditions; product of (Test1)*(Test2)*(Test3)*(Test4) will return 1 and thusTRUE only when all tests return 1 (True). If any of these tests returns False and thus 0, the product will also be 0 (False). So in Calc you can perform a check if all test conditions are met simply by calculating the product of them.

Tip 2. Use an array formulas to iterate over the cell range, not single cell. In you use case you need to loop though all rows in your Shift information table, find rows where all test conditions are met and join the employee’s names from these rows.

If any help/commentary needed, feel free to comment!

[BillEnnals]

Thanks @SM_Riga!. That appears to work perfectly, though it will take me a while to figure out how;) I’ll do some research and let you know if I have any questions.