The formula I am currently using is:
=INDEX('2025'!D:D,MATCH(1,('2025'!A:A<=[@[Pay_Period_Ending_Date]])*('2025'!B:B>=[@[Pay_Period_Ending_Date]]),0))
What I am trying to do lookup calendar dates between a two week period from our Payroll Calendar that represents a pay date to timesheet worked date. The timesheet worked date will have many entries for an employee that are used to calculate the hours paid in a pay period.
Is there a better way to do this?
Difficult to judge if there is "a better way" without seeing the file. Though, in general, one should avoid using entire-column references like D:D, A:A etc.
Furthermore, that formula will only return 1 value (if I'm not mistaken), whereas you mention to lookup multiple dates.
I was able use the FILTER function to obtain the correct result in my spreadsheet. Thanks.
Great that you resolved it yourself, though I would still like to share two other solutions.
The first considerable boosts performance (at least on my old MacBook Air) and that's by eliminating the whole-column references in K. The other is to use XLOOKUP as included in column O.
