So, I am trying to use the VLookup function to find the value of a cell that corresponds with todays date and return that cell information. However, if there is no date given for todays date, then I want it to find the next closest date before today's date. Right now, my simple sheet looks like the picture below:

enter image description here

I want to use a VLookup function in C2 to return the value of the cell in Column B that corresponds to todays date. However, if it returns N/A, then I want it to return today()-1. If that is N/A, then it should return today()-2, and so on and so forth until it hits a date that last had a lesson.

Right now, the formula I have in C2 is =IF(ISNA(VLOOKUP(TODAY(),A2:B1000,2,FALSE)),VLOOKUP(TODAY()-1,A2:B1000,2,FALSE),VLOOKUP(TODAY(),A2:B1000,2,FALSE))

This function only goes back one day if it returns N/A and I can't figure out a formula to get it to lookup the value that's closest to today's date. Any help would be greatly appreciated. If there's another easier way to make this work, then I'd be open to that as well.


1 Answer 1


Use true as the fourth parameter to vlookup(), like this:

=vlookup(today(), A4:B, columns(A4:B), true)

See the vlookup() help page.

  • Thank you! I can't believe it was that simple! May 29, 2021 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.