Search code examples
excelexcel-formulacriteriaxlookup

How can I find the previous date/value with LOOKUP function based on a criteria?

I've been scratching my head round this thing.

Table

I need to perfom the difference between dates, the current date minus the preceding date, while matching an ID (in this case a plate).

I've found a workaround building up the following formula:

Diff = =+IF([@Matrícula]=[@Matrícula];([@[date&time]]-XLOOKUP([@Matrícula];[Matrícula];[date&time];0;0;1));"")

The problem lies (I believe) with the xLookup part: it fixes the first log, then it subtracts the current value from there.

date&time = =+[@Fecha]+[@Hora]

An expected result would be:

enter image description here

I've thought of an Index column or a MAX function into it, but I'm still a newbie...

Thanks for any comment and help.

Solution

  • Following your idea of using Xlookup, I would use it in a reverse search like this, checking for a matching Matricula where the row number is lower. Assumes dates are in ascending order:

    =IFERROR([@[Date&Time]]-XLOOKUP(1,([Matricula]=[@Matricula])
   *(ROW([Matricula])<ROW([@Matricula])),[Date&Time],,0,-1),"")

    enter image description here