Using a FILTER function in conjunction with other functions

Question

sample sheet

I am looking for a function to display the Title (from column B) that has the lowest numerical average in column (found from column G). I have that already in the form of

=INDEX(B2:G,match(min(G2:G),G2:G,FALSE),1)

but I would like to further specify it so that it only grabs the Title with the lowest average, that is also of a certain Category. So let's say I'd want a function to search for the Title with the lowest average only in Category A, I would want that to display "B" instead of "K", which the above function outputs.

Answer 1

Do unmerge I2:I. And fill all corresponding rows with category name to use FILTER.

Answer 2

• CAT A: =INDEX(B2:G5; MATCH(MIN(G2:G5); G2:G5; 0); 1)

• CAT B: =INDEX(B6:G11; MATCH(MIN(G6:G11); G6:G11; 0); 1)

• CAT C: =INDEX(B12:G15; MATCH(MIN(G12:G15); G12:G15; 0); 1)

https://docs.google.com/spreadsheets/d/

Answer 3

• CAT A: =INDEX(B2:B; MATCH(MIN(FILTER(G2:G; I2:I = "Category A")); G2:G; 0))

• CAT B: =INDEX(B2:B; MATCH(MIN(FILTER(G2:G; I2:I = "Category B")); G2:G; 0))

• CAT C: =INDEX(B2:B; MATCH(MIN(FILTER(G2:G; I2:I = "Category C")); G2:G; 0))

https://docs.google.com/spreadsheets/d/