Display name of highest score on a particular date

MrNoobster

New Member
Good Evening All,

I have myself a customised version of the below formula which gives me the information I require, however I would like to add in date.
So as per the example below, we have Thomas who scored 128. I want it to read Thomas scored 128 on (date). The date would be on a range of cells but I'm not sure where to add this into the corresponding formula.

My formula is =INDEX(B18:B3000,MATCH(MAX(AB18:AB3000),AB18:AB3000,FALSE),)&" with "&MAX(AB18:AB3000)&" trailer moves on " I want to add "on (specific date in a range of cells that correspond to the name)

Can anyone help?

Many thanks

Please enter this formula: =INDEX(A2:A14,MATCH(MAX(B2:B14),B2:B14,FALSE),)&" Scored "&MAX(B2:B14) into a blank cell where you want to display the name, and then press Enter key to return the result as follows:

Peter_SSs

MrExcel MVP, Moderator
Peter, you've been incredibly helpful and I can't thank you or this forum enough. Thank you so much for all your time and effort with this
You're welcome ...

... but did you want to pursue this or not?
Excel 2010 does not lend itself to a process like that if you wanted multiple results in a single cell. We can do it if it is okay to produce each of the multiple results in its own cell. Is that acceptable?

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

MrNoobster

New Member
Yes please. Also I would have wanted the results in multiple cells anyway as opposed to a single cell as I'm still working on the format and appearance of this worksheet.

Many thanks

Peter_SSs

MrExcel MVP, Moderator
This is how I would do it then.
- Put the max of AB in a cell somewhere (K1 for me) so that it doesn't need to be recalculated multiple times in each formula,
- Copy the formula in J2 down as far as you might ever need. You may need to put this somewhere else on your sheet as there are not that many vacant cells below J2 in the sample file, If you do move it, just make sure the J\$2:J2 in the top formula refers to the same cell that top formula is in and the K\$1 refers to the cell where we calculated the MAX for column AB.

Book1
ABJKAB
1NameTotal Moves to DateMost moves on a single shift:51
2Ollie Calcott368Ollie Calcott with 51 trailer moves on Monday 6th January
3Mark Whitehead65Nigel Southwood with 51 trailer moves on Monday 20th January
4Alan Smith83
16
17DateShunter06:00 - 07:0007:00 - 08:00Total moves for shift
18Wednesday 1st JanuaryOllie Calcott30
28Monday 6th JanuaryOllie Calcott51
60Monday 20th JanuaryNigel Southwood51
61Monday 20th JanuaryOllie Calcott0
High Score on Date (2)
Cell Formulas
RangeFormula
K1K1=MAX(AB18:AB3001)
J2:J4J2=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B\$18:B\$3001)/(AB\$18:AB\$3001=MAX(AB\$18:AB\$3001)),ROWS(J\$2:J2)))&" with "&K\$1&" trailer moves on "&INDEX(A:A,AGGREGATE(15,6,ROW(B\$18:B\$3001)/(AB\$18:AB\$3001=MAX(AB\$18:AB\$3001)),ROWS(J\$2:J2))),"")

MrNoobster

New Member
That is such a simple solution, I would have thought it would be much more complicated!

I have now integrated this into my spreadsheet Thank you once again Peter for all your hard work and effort into this, appreciation is an understatement!

MrNoobster

New Member
What if I were to use the Large function to find the top 3? Maybe this would also solve the issue of multiple exact results also?

Peter_SSs

MrExcel MVP, Moderator
What if I were to use the Large function to find the top 3? Maybe this would also solve the issue of multiple exact results also?
Whether it solves or repeats the problem of multiple exact results depends on what you would want returned for 'top 3' for an example like this simplified one?

Book1
ABC
1NameScoreRank
2Ken503
3Ben522
4Ann551
5Jen503
6Tim488
7Joe503
8Jim503
9Tom479
10Sam503
Top 3 Sample

Replies
1
Views
348
Replies
2
Views
189
Replies
25
Views
960
Replies
0
Views
93
Replies
6
Views
470

1,127,736
Messages
5,626,596
Members
416,194
Latest member
Dhisilva_aguiar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back