Menu
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.
442
To get the position of the maximum value in a range (i.e. a list, table, or row), you can use the MAX function together with the MATCH function. Which returns the number 4, representing the position in this list of the the most expensive property. The MAX function first extracts the maximum value from the range C3:C11.
The MAX function in Excel returns the highest value in a set of data that you specify. The syntax is as follows: MAX(number1, [number2], …) Where number can be represented by a numeric value, array, named range, a reference to a cell or range containing numbers.
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))
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.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
