CODEWITHSUNDEEP
exceldateexcel-formuladate-rangeindex-match
Captain C
Captain C

Reputation: 201

Find Corresponding Value Based on Today's Date Falling Between Week-Ending Dates in Excel

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

This has been my attempt:

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

Upvotes: 0

Views: 171

Answers (2)

Ike
Ike

Reputation: 13064

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.

Upvotes: 3

Black cat
Black cat

Reputation: 6299

Try this the dates must be in ascending order in row 2.

=HLOOKUP(TODAY(),B2:T3,2,TRUE)

Upvotes: 1

Related Questions