Faux HLookup
I would appreciate some help with an Index match (I think) formula that will pull match off of column. In example below, I need the "Previous User" field to populate with the User who last used the same box. I feel it should be super simple but I'm missing something. Thanks in advance.
p.s. Sorry for using excel in example : )
Answers
-
Hi @Dave Schierman
Hope you are fine, you need to add a system helper column "Modified(Date)"to stamp the date of use of this box then use the following and convert it to a column format formula:
=JOIN(COLLECT(User:User, [Box Number]:[Box Number], [Box Number]@row, [Modified(Date)]:[Modified(Date)], <[Modified(Date)]@row), ", ")
and the formula will sort the user from new to eldest the following screenshot shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thanks Bassam, Works but how do I grab just the most recent name prior?
-
I suggest adding three helper columns.
A system generated auto-number column (no need for special formatting or anything).
A text/number column called [Row Number] with the following column formula:
=MATCH([Auto-Number Column]@row, [Auto-Number Column]:[Auto-Number Column], 0)
A text/number column called [Helper] with the following column formula:
=MAX(COLLECT([Row Number]:[Row Number], [Row Number]:[Row Number], @cell < [Row Number]@row, [Box Number]:[Box Number], @cell = [Box Number]@row))
Assuming that the most recent user is going to be at the top of your sheet, you would then use a formula such as this to pull the previous user:
=INDEX(COLLECT(User:User, [Box Number]:[Box Number], @cell = [Box Number]@row, [Row Number]:[Row Number], @cell = Helper@row))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!