How to return only the most recent entry before a targeted date range?

JJL

Hello,

I currently have the following asset booking sheet:

What I'm trying to do is, for the column 'Assignee in Possession Before Your Booking Date:', if the asset is available within the booking date range that the user has entered -- I would like the column to display what assignee will have the asset last (most recently?) before the booking range that the user has entered.

My current formula is:

=IF([Asset Available During Your Booking Date Range?]@row <> "AVAILABLE", "", JOIN(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], [Asset ID:]@row, [Asset Available During Your Booking Date Range?]:[Asset Available During Your Booking Date Range?], "AVAILABLE", [Asset Booking Start Date:]:[Asset Booking Start Date:], @cell < [Asset Booking Start Date:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell < [Asset Booking Start Date:]@row)))

Unfortunately, what my formula is doing is joining and displaying every single assignee that will have the asset before the user's entered booking date range. E.g., In the image above, for the fourth row, the column should only display 'John' but instead is displaying 'JohnLucas'.

I've tried incorporating the MAX function into my formula to try to get the most recent assignee, but have had no success thus far. Additionally, as the sheet's entries will be erased every other month or so, there will be cases where no one will be in possession of the asset before the user's booking date range in which case my formula is just returning a blank cell -- instead, I'd like it to say something like "NO PREVIOUS ASSIGNEE(S)"

Any help is greatly appreciated, thank you!

Paul Newcome

You will need a helper column to first grab the nearest end date prior to the row's start date.

=MAX(COLLECT([Asset Booking End Date:]:[Asset Booking End Date:], [Asset Booking End Date:]:[Asset Booking End Date:], @cell < [Asset Booking Start Date:]@row, [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row))

Then in your display column you would use this:

=IFERROR(INDEX(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell = [Helper Column]@row), 1), "NO PREVIOUS ASSIGNEE(S)")