I have an Excel table that ranges from cells B2 to T11. The first row in this range (B2:T2) contains dates, which are essentially week-ending dates representing Fridays of each week. I need to create an Excel formula to find out which week-ending date encompasses today's date and return the corresponding value from B3:T3.
The dates in B2:T2 are not necessarily matching today's date exactly, but I want to check if today's date falls within the range from the previous week-ending date to the current week-ending date. Once I find that range, I'd like to pull the corresponding value from row 3 (B3:T3):
=INDEX($B3:$T3, 1, SUMPRODUCT(--($B$2:$T$2 >=TODAY() - WEEKDAY(TODAY(), 3)), --($B$2:$T$2 <= TODAY() + (6 - WEEKDAY(TODAY(), 3)))))
I hope someone can help me with this.
If you have Excel 365 you can use
=XLOOKUP(TODAY(),B2:T2,B3:T3,"-",1,1)
The second last 1 tells Excel to look for an exact or greater date.