Using MAX in a Filter range in google sheets-CodePudding

 Column A         Column B

    Joe             23
    Tom             102
    Sally           99
    Tom             111
    Joe             67

How to I output:

Tom           111

This isn't working.

=filter(MAX(B1:B,A1:A="Tom")

I am just adding text here because it is saying most of my post is code........blah blah blah.

CodePudding user response：

I'd recommend this:

=SORTN(FILTER(A:B,A:A="Tom"),1,0,2,0)

FILTER filters in just rows where Col B = "Tom".

The SORTN parameters mean "Return the top 1 results with ties-mode 0 (which is irrelevant when there's only one person) based on sorting column 2 in order 0 (which means descending order, i.e., max to min)."

CodePudding user response：

here's how you do it with max and filter:

=FILTER(B:B, A:A="Tom", B:B=MAXIFS(B:B, A:A, "Tom"))