Microsoft
The Search_Mode is really obscure and will rarely be used. I cannot even come up with a realistic scenario for it, though I am sure there are some.
= IF( [@Status]="Clock Out",
[@[Event time]] -
XLOOKUP( [@[Access card number]],
[Access card number] * ( [Event time] < [@[Event time]] ),
[Event time],
[@[Event time]],
0,
-1 ),
"" )The above might provide an example. It is taken from a table of clock-in / clock out times. The objective is to calculate a shift length each time the employee clocks out. The search is for a matching access card number with earlier timestamp. By searching from the end, the most recent event is returned.
It is not such a tragedy if the user feels obliged to provide a response to the 'if not found' parameter. Here 'clock out' without at prior 'clock in' would cause an error but I chose to deal with it at source by returning the current event clock time in order to give a zero duration for the shift. The alternative is to raise a deliberate error and then trap it with IFERROR but that is neither obviously simpler nor better.