Lookup previous date from timestamp

**robonuk** · 06-28-2018, 01:11 PM

I'm trying to build a formula that will look up a customers name depending on a trailer number & timestamp. The timestamp would be automatically input when the trailer is processed at our depot, so the timestamp of the process & delivery would never match. I have attached a basic sheet, but the gist of it is below.

Trl1 delivered to Fred at 15:55 on 27/06/18 but we didn't process the trailer until 14:43 on 28/06/18. Is there a way I could get excel to find the previous customer from the point of processing? Trl1 will possibly be used several times a day so it would need to lookup the last possible customer available, sometimes over multiple days.

I can't get the excel file to upload but a screenshot should hopefully be attached.

Maybe I'm asking Excel to be too complex here.

**shg** · 06-28-2018, 01:32 PM

Please take a few minutes to read the Forum Rules about thread titles, and then amend yours accordingly.

Thanks.

EDIT: Thank you.

**shg** · 06-30-2018, 12:21 PM

Please post a workbook with an example of data layout and expected results.

**robonuk** · 06-30-2018, 04:26 PM

I can't attach a file it simply gives me a small white box with nothing to select the file.

Can't do images or excel file. Tried both Google Chrome & Microsoft Edge

**shg** · 06-30-2018, 04:42 PM

The forum rules explain how to attach a file using the Manage Attachments button.

**MarvinP** · 06-30-2018, 05:03 PM

Hi robonuk,

See if using a sorted by date and helper column solves your problem. I've created some sample data and given you an answer in the attached. The column D formula needs to be entered using :
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Previous Date based on Driver CSE .xlsx