Index/Match or Lookup - return column from row where "x" matches and is the most current

Joseph Boes

Hi All,

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?

Connor Hartford

Hi Joseph,

Please post the formula you are using.

Without seeing your formula I don't know for sure, but are you using the Search Type field in the Match function? In your case it sounds like it should be (0), meaning "not sorted".

MATCH(search_value, range, [search_type])

The default is 1. The manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1). All of this information comes from this article: https://help.smartsheet.com/function/match

Connor

Joseph Boes

Yes, using the 0 for not sorted, which returns values fine, but since it is only looking to match A/B/C, it ends up returning the first instance that it finds. Which works fine when "ThisSheet" is sorted by date (as the first listing is the right one then), but if things got unsorted there just isn't anything else in my formula to tell it which A/B/C to return

Lets call the sheet I pasted in above "Reference" and call the other sheet (where formula is) as "Main"

=INDEX({Reference A/B/C}, MATCH(Main A/B/C 1, Reference A/B/C, 0 )

But I did realize my initial screenshot had an error - I switched the order of C row return values, but forgot to also change date order. What I hope for is a formula that will always return the value 2 (because it is associated with most recent date), even if it was not sorted: