Index/Match or Lookup - return column from row where "x" matches and is the most current
I feel like I might just be missing some simple solution here... but I'm wanting to lookup from one grid into another where I keep some subset data and there will be multiple rows that match the lookup value, but I want it to return the one where the date (in another column) is the most current. It works fine when I sort the grid descending (as it pulls the first value), but I prefer not to rely on sorting - because if data gets resorted it could return incorrect information to the other sheet.
Example screenshot below... I want to look up values A, B, and C, and I want the value "2" returned for each. And that would work for A & B given the sorting, but for C it would return "1" because that is listed first:
I'm just using a general Index/match combination right now (looking for A, B, C respectively)... is there something I can add within that to look for A, B, C but return the row number from the match that has the most current date?
Help Article Resources
Check out the Formula Handbook template!