Find Max in array- return corresponding cell

  • Thread starter Thread starter Lew_Cousineau
  • Start date Start date
L

Lew_Cousineau

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
 
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)
 
Add a column to the right of your array where every cell has a max function to find the maximum number in the row.
(That should be column E in your case)
In E2, write =max(B2:D2)
In F2, write =INDEX(1:1,E2,B2:$D2,FALSE))
:::
down to E5 and F5
In E5, write =max(E2:E5)
In F5, write =Index(F2:F5,E5,F2:F5,false)
You will see "Lew" in F5.
 
Back
Top