Find Max value horizontally and vertically in table

[SHI.NL]

Hi!

I have a table with dates on the first column and cities spread over the rows.
Now I would like to search on the row of most recent date for the max value (aka city with most clicks). In this case it should return 7 (Amsterdam on 9/17/17). Any ideas??
Thanks!
Capture3.JPG


Date New York Amsterdam Rome
9/9/17 2 2 4
9/10/17 3 4 6
9/11/17 5 6 2
9/12/17 6 7 2
9/13/17 7 4 4
9/14/17 3 3 5
9/15/17 2 4 6
9/16/17 5 7 3
9/17/17 4 7 2

[Pete_UK]

Try this:

=MAX(INDEX(B:D,MATCH(MAX(A:A),A:A,0),))

You might need to use semicolons ( ; ) instead of commas ( , ) in the formula.

Hope this helps.

Pete

[kev_]

or this

=MAX(INDIRECT("B"&MATCH(MAX(A:A),A:A,0)&":D"&MATCH(MAX(A:A),A:A,0)))

[samba_ravi]

try
=MAX(INDEX(B:D,MATCH(99^99,A:A),))

[samba_ravi]

or
=MAX(INDEX(B:D,MATCH(MAX(A:A),A:A,0),))

[kev_]

nflsales suggested:
=MAX(INDEX(B:D,MATCH(99^99,A:A),))
which uses NON-EXACT match to find the last entry in column A
- this solution requires dates to be in ascending order

[SHI.NL]

Great! Thanks a lot! Works like a charm.
One more though, how do I add the corresponding city now?

[SHI.NL]

Try this:

=MAX(INDEX(B:D,MATCH(MAX(A:A),A:A,0),))

You might need to use semicolons ( ; ) instead of commas ( , ) in the formula.

Hope this helps.

Pete

Great! Thanks a lot! Pete_UK ! Works like a charm.
One more though, how do I add the corresponding city now?

[Phuocam]

City =INDEX($B$1:$D$1,MATCH(MAX(INDEX(B:D,MATCH(MAX(A:A),A:A,0),)),INDEX(B:D,MATCH(MAX(A:A),A:A,0),),0))

[SHI.NL]

City =INDEX($B$1:$D$1,MATCH(MAX(INDEX(B:D,MATCH(MAX(A:A),A:A,0),)),INDEX(B:D,MATCH(MAX(A:A),A:A,0),),0))

Thank you !!!!! Awesome !!!!

[Pete_UK]

Glad you got a solution to your follow-on question, and thanks for the rep earlier.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Pete