Help with Formula

[woodsmeizter]

I am trying to look through a years worth of data to find who the top performer was and show their total score between January 1st and January 31st of 2021. I have managed to use MAXIF to find the best value within the date range but im struggling to work out how to show which person that score relates too.

Column A is the persons name
Column E is the date score was recorded
Column W is the total score

C17 is where i want the persons name to show, and D17 is where i have the top score showing, how do i get it to find the name from the score it has found within the two dates?

=MAX(IF((Database!E:E>=$H$2)*(Database!E:E<=$I$2), Database!W:W, "")) This is the formula i used to find the top value.

 

[Fluff]

Hi & welcome to MrExcel.
Could you have two or more people with the same score? If so what do you want returned?

 

[woodsmeizter]

Yes we could really as the score is out of 24 so people could have the same result. So if it can find everyone with that score for that time period that would be brilliant. I have the Start of the month in H2 and The end of the month in I2. As the sheet pulls loads of other stuff from the dates I enter in there..

Thank you so much

 

[Fluff]

Ok, how about
In D17 for the max score

=MAX(FILTER(Database!W2:W2000,(Database!E2:E2000>=H2)*(Database!E2:E2000<=I2)))

and in C17 for the names

=FILTER(Database!A2:A2000,(Database!E2:E2000>=H2)*(Database!E2:E2000<=I2)*(Database!W2:W2000=D17))

If there is more than one name the formula will spill down in C18 C19 etc.

 

[woodsmeizter]

Ok, how about
In D17 for the max score

=MAX(FILTER(Database!W2:W2000,(Database!E2:E2000>=H2)*(Database!E2:E2000<=I2)))

and in C17 for the names

=FILTER(Database!A2:A2000,(Database!E2:E2000>=H2)*(Database!E2:E2000<=I2)*(Database!W2:W2000=D17))

If there is more than one name the formula will spill down in C18 C19 etc.

That is amazing, if i wanted it to go further than 2000 columns could i just put W:W?

 

[Fluff]

I would recommend against using whole column references as they can slow the workbook to a crawl.
You can change the 2000 to any number you want, but I would recommend against making the range too much larger than your data.