Searching for Last Time Event Happened in Range

[alexrawnsley]

I have a large excel workbook, extensively linked for the hockey team I work with. Each player has their own sheet, with a game-by-game log of all their goals, assists, penalties, etc. This is all then bought together in a top sheet with all 21 players having all their stats summed up, showing me leaders in all categories, plus team totals.

I want to be able to have on my top sheet, the last time a certain player scored a goal, and who the opponent was.

My player sheets are set up as such in the attachment

So A4 is the game number (not really relevant in this situation)
B4 is the date of the game
C4 is the opponent

So on my top sheet, in say cell F3, I want it to look at the goals column (E4:63) on the sheet of the player in question (Smith), and find the last time a value was greater than '0'. Once it finds that value, I want it to take the value of column B on that row (where the value >0) and put it in F3, then the value in column C and put it in G3 on the top sheet

Make sense? Possible?

test.xlsx
Here's the sample file. Under this current setup, I would want F3 on Top Sheet to look at E4:E63 on 2 SMITH and return 'Nov-05'. Likewise with G3 on Top Sheet, with 'vs PGSK'. If the player were to score a goal (ie: >0 in E23), then F3/G3 would reflect that change.

[DGagnon]

could you please attach a worksheet with sample data, this will make it easier for members of the forum to assist.

[DGagnon]

try using somethign like this confirmed with Ctrl+Shift+Enter.

=MAX(IF(E3:E11>0,B3:B11,0))

once confirmed it should look like this:

{=MAX(IF(E3:E11>0,B3:B11,0))}

[DGagnon]

Try using this solution confirmed with Ctrl+Shift+Enter, be sure to modify the range to suit your actual range.

=MAX(IF(E3:E11>0,B3:B11,0))

[alexrawnsley]

could you please attach a worksheet with sample data, this will make it easier for members of the forum to assist.

I'll attach a sample in the top post.

[DGagnon]

in Cell F3, confirmed with Ctrl+Shift+Enter:
=MAX(IF('2 SMITH'!E4:E63>0,'2 SMITH'!B4:B63,0))


In Cell G3:
=VLOOKUP(F3,'2 SMITH'!B:C,2,0)

[alexrawnsley]

DGagnon

I've tried your above formula. It returned beautifuly on the first test. I put a '1' in the E4 column of 2 SMITH, and the cell returned Sep-23. However when I put a '2' in E9, it still returned Sep-23.

Edit: Just saw you latest reponse, I'll try that.

Edit 2: Just tried it, same problem. Thanks for your help to date, hopefully you can help figure this out!

[DGagnon]

The years on your dates are incorrect, the actual date in cell E9 is 9/1/2023, Sep-2023. you just need to update the dates of the games.

[alexrawnsley]

Done some more tests. The formula works perfectly with the first result. No matter where the '1' goes in the E column of 2 SMITH, it returns the correct result. It's just when you place a 2nd number in a cell below, that it doesn't return

[alexrawnsley]

Oh my god it's beautiful! Thank you so much!!! I'll fix the dates and we'll be good to go!!!

Thanks, I'll mark this solved

[DGagnon]

this goes back to your date's it is looking for the greatest date, but you have the day in the year portion of the date, instead of 23-Sep-2011 you have 1-Sep-2023

[alexrawnsley]

Understood. Those dates were a copy/paste over from another spreadsheet which I just use for text entry. Thanks again for the help!