Lookup from a date to a date range

[jomili]

Some days I'm much stupider than others. This is one of those days.

In the attachment I have mileage expenses by the period in our fiscal year in which they were paid out (like "2024_01","2024_02").
I also have the "Service Date", the date they actually did the driving.

For each time period in which they drive (not when they get paid for it) they have a travel rate (cents per mile).

I need to convert these expenses to the miles they traveled (that info doesn't show in our expense files), which is the simple equation "Expense/Rate".
So, I need to do a lookup from the Service date to the corresponding date range to return the rate in place at the time.

That's where I'm drawing a blank, how do I lookup from a date to a date range?

If it helps to redo my little rate table that's not a problem, I haven't redone because I can't yet figure out how to approach this problem.

Any help would be appreciated.

[Glenn Kennedy]

DFPS appears where???

[jomili]

"DFPS appears where???"

Not sure what your question means. I work for DFPS, but don't see anything about that in my question.

[Glenn Kennedy]

Its mentioned in the big yellow table with dates and rates....

[jomili]

ah, that's where. It's the rates for all Texas Government entities. For one very short period DFPS had it's one rate.

I'm heading away, won't be able to reply again until tomorrow.

[Glenn Kennedy]

No problem I'm away for the night, too.

[romperstomper]

Here's the basic approach with a lookup table.

[jomili]

Thanks Rorya, that seems to get it. But one thing I noticed, your rate table is sorted frorm Oldest to most recent. if I sort my rate table from most recent to oldest the formula doesn't work right. So should I always have my rate table sorted from Oldest to most recent?

[AliGW]

No need if you add SORT to the formula:

=LOOKUP(A6,SORT($N$5:$O$12,1,1))

[jomili]

Sweet! So it's always sorted, no fuss, no muss!

[romperstomper]

You could also use XLOOKUP and tell it to find the value or the closest lower number:

=XLOOKUP(A6,$P$5:$P$12,$Q$5:$Q$12,,-1)

[AliGW]

Glad to have helped.

Thanks for the kind rep comment.