Return Max on Multiple Columns

[mikewongbiz]

Hi all, I am a newbie to Excel and haven't been able to work this out:

Name Results1 Results2 Results3 Results4
Tom 55 80 45 60
Ken 70 67 48 90
Craig 40 95 47 75

Q1: Any ideas of a formula to search for the MAX in the range B2:E4 and Return the name from A Column.

Eg. the MAX in the numbers above is 95 which is found in C4 and will return Craig.

I can achieve only with one column using Index and Match, but, not with multiple columns. Any suggestions/help much appreciated !

Thanks,
Mike

 

[Marcelo Branco]

Hi Mike

Welcome to Mr Excel

Maybe something like this


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]

Name​

[/td][td]

Results1​

[/td][td]

Results2​

[/td][td]

Results3​

[/td][td]

Results4​

[/td][td] [/td][td]

Formula​

[/td][/tr]

[tr][td]

2​

[/td][td]

Tom​

[/td][td]

55​

[/td][td]

80​

[/td][td]

45​

[/td][td]

60​

[/td][td] [/td][td]

Craig​

[/td][/tr]

[tr][td]

3​

[/td][td]

Ken​

[/td][td]

70​

[/td][td]

67​

[/td][td]

48​

[/td][td]

90​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

4​

[/td][td]

Craig​

[/td][td]

40​

[/td][td]

95​

[/td][td]

47​

[/td][td]

75​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

5​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in G2
=INDEX($A$2:$A$4,MAX(INDEX((B2:E4=MAX(B2:E4))*(ROW(B2:E4)-ROW(B2)+1),)))

Hope this helps

M.

 

[mikewongbiz]

Hi Marcelo Branco,

You are great! It works like a charm...

Mike

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.