Index/Match or Lookup - return column from row where "x" matches and is the most current
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?
Answers
-
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
Connor Hartford
-
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:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!