Minimum value between dates

[Excellling]

Hi All

I have been looking around quite a bit to find a formula for my specific purpose in relation to returning a minimum value between dates.

Basically I would like a formula to return the Pay # when today() date is equal to or less than the pay date (but the next pay date has not happened yet).

For example, If Today() is 2/3/2023 then return 18, and return 18 everyday until today() becomes 16/3/2023 and then start returning 19.

So far I have tried MINIFS, Min with multiple criteria and tried to combine MAX and MIN - not having too much luck. I also tried offset function to try to look at the next row in the Pay Dates column to work out whether the next pay date is current or not.

Hoping this makes sense

I have attached a little spreadsheet to assist and appreciate any advice - thank you

 

[Tetra201]

Maybe

=LOOKUP(A2,B2:B27,C2:C27)

 

[Excellling]

Thank you kindly Tetra201

This works perfectly.

Just for my learning purposes, what exactly does the Lookup function do? By what I just looked up (after you sent your assistance) it brings back a corresponding value in a column to the right, but when the date moves to 3/3/2023 it is doing what I want and still returning 18 but I don't really understand why.

Regards

 

[Tetra201]

From the LOOKUP function help:

• If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value

So, if 3/3/2023 cannot be found, the function matches 2/3/2023 and returns 18.

 

[Excellling]

Thank you that makes perfect sense and I now understand what it's doing for next time.

 

[Peter_SSs]

@Excellling
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have a recent version with XLOOKUP then you could also investigate that as it has a number of features that may benefit you in other circumstances. For this task it would be

=XLOOKUP(A2,B2:B27,C2:C27,,-1)

 

[Tetra201]

Glad to have helped.