CODEWITHSUNDEEP
indexingfiltergoogle-sheetsmatchgoogle-query-language
Ken
Ken

Reputation: 1021

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

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.

Upvotes: 0

Views: 97

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

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.

Upvotes: 0

JPV
JPV

Reputation: 27242

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.

Upvotes: 1

Related Questions