Google Sheets - Return all cells based on nearby reference cells/multiple criteria

Question

I'm attempting to create a table which shows averages based on multiple criteria of nearby cells.

The best way to explain is with my sample sheet (with edit permission): https://docs.google.com/spreadsheets/d/1R3WSa2B0_wzOsp4Z7ktVy_2UVG2tVgo0DK1hehMNW2Q/edit?usp=sharing

The closest I've gotten was to utilize Index Match, though that only returns a value based on a single criteria:

=index(C1:C10, match("Joey",C1:C10,0)+2)

But of course I need to reference multiple cells/criteria (looking at the "Assigned Person" and the "Job Type"... THEN pulling the value of the cell labeled "Days")

I've also tried a pivot table, HLOOKUP, Filter, and Query, but can't seem to get the multiple criteria AND cell reference in there.

Answer 1

Maybe try

=iferror(average(filter(offset($C$2:$C$10, 2, 0), $C$2:$C$10=$A15, offset($C$2:$C$10, 1, 0)=B$14)), "-")

in B15 and fill down and to the right.

Answer 2

I'm looking at something like this at the moment to pull out row 2,5,8 then 3,6,9 then 4,7, 10 (looks a bit long):

=query({filter(C2:C,mod(row(C2:C)-2,3)=0),filter(C2:C,mod(row(C2:C)-2,3)=1),filter(C2:C,mod(row(C2:C)-2,3)=2)},"select Col1,avg(Col3) where Col3 is not NULL group by (Col1) pivot (Col2)")

Then this to get the averages without grouping them by Assigned Person

=query({filter(C2:C,mod(row(C2:C)-2,3)=0),filter(C2:C,mod(row(C2:C)-2,3)=1),filter(C2:C,mod(row(C2:C)-2,3)=2)},"select avg(Col3) where Col3 is not NULL pivot (Col2)")

However you can break this if you change the number of rows in the sheet, because the three arrays in curly brackets could have different numbers of rows.