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 : )


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    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:

    PMP Certified


    ☑️ 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?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!