I feel like my question should be easy to figure out, but I've looked around and can't seem to find out how to get a basic array spill function that produces the max value. Here's my simplified data set:

My objective is to have a unique list of food (from Col A), that returns the max corresponding Value from Col B. The formula for unique list from Col A is easy... =UNIQUE(filter(A:A,A:A<>"")), what I'm struggling with is getting a dynamic maxifs to align with this.

To illustrate, if I put the unique function in cell D2 (thus it would spill to d4 as shown below in blue), a correct corresponding non-array function would be =MAXIFS(B:B,A:A,D2) (shown in column e). I could drag this down the remaining rows but I would like this to be dynamic as there may be more food in my data set in the future.

What I would EXPECT to work is... =filter(MAXIFS(B:B,A:A,D2:D),D2:D<>"") but this returns #Value!. By comparison, if I were to use sumif/Average, =filter(SUMIF(A:A,D2:D,B:B),D2:D<>""), I get what I WOULD expect (which really confuses me).

Is there a way to get a dynamic maxifs (or any function that produces an equal value in column E) that would spill based on unique values in column D?

CodePudding user response：

try:

=QUERY({A:B}, "select Col1,max(Col2) where Col2 is not null group by Col1 label max(Col2)''")

bonus:

=QUERY({A:B}, "select Col1,max(Col2),sum(Col2) where Col2 is not null group by Col1 label max(Col2)'',sum(Col2)''")

bonus 2:

=SORTN(SORT(A1:B, 2, ), 9^9, 2, 1, 1)

2       - sort the second column of range A1:B
<empty> - or 0 or FALSE = "in descending order"
9^9     - output all rows
2       - 2nd mode of SORTN = "group by..."
1       - 1st column
1       - in ascending order