Find the value in a column next to the last date before today

I need to pull back a value into a cell telling me what the current license fee is for my project. The license fee varies by year and steps up on the anniversary date. I need to find the value for the current year from the list shown below. I am sure there is a simple way to do it, but haven't been able to hack it from the formulas I have found so far.

Thanks,

Mike


Table.JPG

By the way, I realise that in this particular table all the values are the same, but I have mutiple sheets, one per license and for other licenses the fees do change!

Try this solution

Capture.PNG

Thanks for the advice. See the attached spreadsheet which hopefully illustrates the problem I have more clearly.

find value related to date.xlsx

Sorry JPR, your png isn't displaying for me here.

Okay, take a look at the attached

That's great, thanks. I struggled t get it to work at first, I think that the way I was changing the definition of the arrays was breaking the system, but when I dragged the array area over and re-defined it that way it worked perfectly. Many thanks.

I also replaced the definition of a cell with todays date with the term TODAY() and that is working just fine and keeps my worksheet neater! Thanks again JPR

I'm sorry Pepe, but I'm afraid your method did not return the correct answer, I think it returned a value which had the same year as TODAY, which means if the license anniversary date is in september I will be showing an incorrect value for the first six months of the year.

Thankyou for your help though, I am sure that your code may be of help to other people with similar problems.

Mike