Check if date between date range in excel where there are multiple date ranges in a list and output corresponding range label

Question

How can I check if an input_date falls between two dates (a date range) in excel, where the date range exists in a list of date ranges?

So I need to check if input_date falls between any date range, and then if it does, return the value of the date ranged it matched with. See the below table for an example.

Month	Start Date	End Date
Month 1	1/1/2022	1/31/2022
Month 2	2/1/2022	2/27/2022
Month 3	3/1/2022	3/31/2022

Input vs Expected Result

input_date	Expected Result = Month
1/25/2022	Month 1
2/3/2022	Month 2

I've tried using =IF(AND(A2>StartDate,A2<EndDate),TRUE, FALSE) but how can I check A2 against all date ranges in a list, and output the corresponding Month value? Is the best way really just nesting if statements for a fixed number of ranges? Any dynamic approach?

Answer 1

You may try using the either of formulas like shown below,

---

• Formula used in cell B7

=XLOOKUP(1,(A7>=$B$2:$B$4)*(A7<=$C$2:$C$4),$A$2:$A$4,"")

---

If one do not have access to XLOOKUP() then INDEX() & MATCH() can also be used,

---

• Formula used in cell C7

=INDEX($A$2:$A$4,MATCH(1,(A7>=$B$2:$B$4)*(A7<=$C$2:$C$4),0))