finding the max through multiple columns

[mnsportsfan]

I'm looking for a formula that will find the max number. In the attached file I'd like cell D17 to search columns E, N, W, AF, AO, and AX then return the highest number. If possible, I'd then like column C17 to return the employees name from column C.

[Richard Buttrey]

See attached which uses a helper row 17, which could of course be anywhere.

Personally though I'd be inclined to rethink your data layout and create a proper two dimensional normalised database with a column for Dates, then columns A:M

That wwould enable youto analysie your data in a far more efficient way and allow you to use a Pivot Table.

[canapone]

Good morning,

in D17

=MAX(E3:AX9)

it's not robust approach, but numbers in other columns cannot compete with Cases numbers to be the "MAX number"

A more prudent approach could be

=MAX(E3:E9,N3:N9,W3:W9,AF3:AF9,AO3:AO9,AX3:AX9)

To get the name in C17

=INDEX(C3:C9,AGGREGATE(15,6,ROW(3:9)-2/(E3:AX9=D17),1))

To get -for istance -until 5 names that could have reached the MAX

=TEXTJOIN(" / ",1,INDEX(C3:C9,AGGREGATE(15;6,ROW(3:9)-2/(E3:AX9=D17),INDEX(ROW(1:5),))))

Hope that helps

[Bo_Ry]

Please try

C17
=INDEX(C:C,SUMPRODUCT((ROW(E3:BF9)*(E3:BF9=G17))))

D17
=MAX(INDEX($E$3:$BF$9*($E$2:$BF$2=$E$2),))