CODEWITHSUNDEEP
excel
mjm26
mjm26

Reputation: 307

Return row label for max value in excel r

How do I return the value in a column being used to label the rows of a table? For example, given the table below I want to find MAX(A:A) and return "Three" instead of the value 1.

        |A      B      C    D
--------|---------------------------------
One     |0.2    0.2   0.5   0.4
Two     |0.1    0.1   0.6   0.1
Three   |1.0    0.3   0.7   0.2
Four    |0.5    0.4   0.5   0.3

Assume if two values are the same then returning either is acceptable.

Upvotes: 20

Views: 52540

Answers (2)

user3794950
user3794950

Reputation: 23

use the OFFSET and MATCH functions with MAX

OFFSET(A1,MATCH(MAX(A:A),A:A,0),0)

MATCH returns the position of a lookup value in the lookup array.

Upvotes: 2

Doug Glancy
Doug Glancy

Reputation: 27478

Assuming data in rows 1 to 4, put this in B5 to get the answer for column B:

=INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0))

Upvotes: 26

Related Questions