Find Max in array- return corresponding cell
 

I can't figure this out. My data looks like this
Lew Bill Bob
$14,875.00 $6,975.00 $750.00
$19,550.00 $0.00 $7,750.00
$14,350.00 $0.00 $27,425.00
$48,775.00 $6,975.00 $35,925.00

I want a function to find the MAX value of ALL those numbers ($48,775)
and return the name in row 1. (Lew in this case).
The function I see most in this new group seems to only work for
numbers in single rows or single columns.
=INDEX(1:1,MATCH(MAX(A2:K2),A2:K2,FALSE))
Where as my max number could be in any one of different rows or
different columns. I suspect I need an array formula but not sure how
to do it.
Thanks for the help

Find Max in array- return corresponding cell
 

Try this array formula** :

=IF(COUNT(A2:C5),INDEX(A1:C1,MAX((A2:C5=MAX(A2:C5) )*COLUMN(A2:C5)-MIN(COLUMN(A2:C5))+1)),"")

Note: if there are multiple instances of MAX the formula will match the
rightmost instance.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
I can't figure this out. My data looks like this
Lew Bill Bob
$14,875.00 $6,975.00 $750.00
$19,550.00 $0.00 $7,750.00
$14,350.00 $0.00 $27,425.00
$48,775.00 $6,975.00 $35,925.00

I want a function to find the MAX value of ALL those numbers ($48,775)
and return the name in row 1. (Lew in this case).
The function I see most in this new group seems to only work for
numbers in single rows or single columns.
=INDEX(1:1,MATCH(MAX(A2:K2),A2:K2,FALSE))
Where as my max number could be in any one of different rows or
different columns. I suspect I need an array formula but not sure how
to do it.
Thanks for the help