Display name of highest score on a particular date

MrNoobster

New Member
Joined
Dec 18, 2019
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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:

doc display name highest score 1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

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
Joined
Dec 18, 2019
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 18, 2019
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 18, 2019
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top