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:
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.
