Display name of highest score on a particular date

[MrNoobster]

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]

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?

 

[MrNoobster]

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]

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
	A	B								J	K																	AB
1	Name	Total Moves to Date								Most moves on a single shift:	51
2	Ollie Calcott	368								Ollie Calcott with 51 trailer moves on Monday 6th January
3	Mark Whitehead	65								Nigel Southwood with 51 trailer moves on Monday 20th January
4	Alan Smith	83
16
17	Date	Shunter								06:00 - 07:00	07:00 - 08:00																	Total moves for shift
18	Wednesday 1st January	Ollie Calcott																										30
28	Monday 6th January	Ollie Calcott																										51
60	Monday 20th January	Nigel Southwood																										51
61	Monday 20th January	Ollie Calcott																										0
High Score on Date (2)

Cell Formulas
Range	Formula
K1	K1	=MAX(AB18:AB3001)
J2:J4	J2	=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]

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]

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]

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
	A	B	C
1	Name	Score	Rank
2	Ken	50	3
3	Ben	52	2
4	Ann	55	1
5	Jen	50	3
6	Tim	48	8
7	Joe	50	3
8	Jim	50	3
9	Tom	47	9
10	Sam	50	3
Top 3 Sample