I'm trying to more fully automate the calculation of values and summary data in a spreadsheet I keep about the results of matches in a pool league.
I have a table with lots of information about each match, with the relevant fields being: Date of Match, Winner, Winner Starting Handicap, Winner Ending Handicap, Loser, Loser Starting Handicap, Loser Ending Handicap, Match Start Time.
Handicaps are adjusted at the end of every match, and before the next one. It's a pain to find the most recent past record for a player (could have been a Winner or Loser) and copy his ending handicap from that record to the Starting Handicap (winner or loser) for the one I'm now entering.
I'd like a formula that would find the most recent record (highest date & start time in case he played twice in one day) where he was a winner or a loser, and then get the ending handicap (from the respective winner or loser).
Per teylyn's suggestion, here's a Dropbox link to the file. The relevant tab is Match Results: https://www.dropbox.com/s/1j9c6zsxjd3q4dt/Sample%20for%20Excel%20Question%20on%20Superuser.xlsx?dl=0
I added a blank column L to test things, comparing the results to what's in K to see if they were working, that's why it's there. Forgot to remove it when I put it in Dropbox.