0

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!

difab
  • 59
  • 5

1 Answers1

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:

enter image description here

Caveat:

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

CMB
  • 4,950
  • 1
  • 4
  • 16
  • Thank you!! This is great. I used the first formula provided and added FALSE to the end and was able to get what I needed. – difab Nov 12 '21 at 18:02