Finding highest selling item within a category

Question

I am looking to find the highest selling drink within each category in cells C6:C16. I am able to find the highest value in each category with a MAXIFS function, but I want to further pull the corresponding beverage name (for example, Stella Artois for the Beer Category). How do I find that value? Is it a VLOOKUP?

Thanks in advance!

Answer 1

This can be done in multiple ways:

You can expand the formula in column C and use VLOOKUP to get the highest selling item per category:

=VLOOKUP(MAXIFS($C$20:$C$200,$A$20:$A$200,A6),FILTER({$C$20:$C$200,$A$20:$B$200},$A$20:$A$200=A6),3)

Or if you want to reference column C, you can reduce to this:

=VLOOKUP(C6,FILTER({$C$20:$C$200,$A$20:$B$200},$A$20:$A$200=A6),3)

Output:

Caveat:

Note that this needs autofill / dragging down to C16 to get all the needed values.