Faux HLookup

Dave Schierman

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

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:

Dave Schierman

Thanks Bassam, Works but how do I grab just the most recent name prior?

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