Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
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
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 501 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 79 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I have looked through all of the answers for this topic, and still cannot find a solution that works for me. I have a sheet with a multi-select dropdown used to code responses. In another sheet, I wan…
- I have 1 sheet that has duplicated Classroom IDs but different scores on each line. I need to flag any instances during the same term of a difference over 1.00 higher or lower with the same Classroom …
- I have two formulas that are fairly identical but one is producing a #NO MATCH error. =COUNTIFS(Shop:Shop,Shop@row,[Review Start Date]:[Review Start Date], YEAR(@cell)= YEAR([Review Start Date]@row)) …