Search code examples
excelif-statementexcel-formulams-officeexcel-2016

In MS Excel (Office 2016), how do I select a column NEXT to the highest value?

For example, I have 3 sets of values

Names Values
Dog 500
Cat 300
Mouse 350

I want to simply compare the values, and show the "Name" of whichever value is the highest.

I have tried it with a simple IF(A>B;IF(B>C;(IF(C>A);C;A);B;C;B), but the statement fails as soon as the first "False" value is reached.

There MUST be a better way, can someone please help me find it?! :-)

Solution

  • In Microsoft 365:

    =@SORTBY(A2:A4,B2:B4,-1)

    With previous versions of Excel you could try LOOKUP() to return the last name where the value is equal to the max:

    =LOOKUP(2,1/(B2:B4=MAX(B2:B4)),A2:A4)

    Or a combination of INDEX() and MATCH() to return the 1st name:

    =INDEX(A2:A4,MATCH(MAX(B2:B4),B2:B4,0))