Finding if a date range belong to a list of date ranges

[skjohn]

I have 2 excel tables, one has list of employees and their leave start date and end date.. Sample below. The second table has another table with Sprint data(Sprint no, Sprint Start Date, Sprint End Date). I want to check in which sprint the leave dates fall into and return the corresponding sprint no. At the moment I am using ifs formula as below- =IFS( D3=0,"", AND(B3<=$S$2,B3>=$R$2),$Q$2, AND(B3<=$S$3,B3>=$R$3),$Q$3,AND(B3<=$S$4,B3>=$R$4),$Q$4,AND(B3<=$S$5,B3>=$R$5),$Q$5,AND(B3<=$S$6,B3>=$R$6),$Q$6,AND(B3<=$S$7,B3>=$R$7),$Q$7,AND(B3<=$S$8,B3>=$R$8),$Q$8,AND(B3<=$S$9,B3>=$R$9),AND(B3<=$S$10,B3>=$R$10)).. However, this method is very difficult as the number of sprint increases. Is there a better way to do this.

Another formula I tried was

=IFERROR(INDEX(Sprint_Info_leave[#All],IF((Sprint_Info_leave[Start Date]<=$B3)*((Sprint_Info_leave[End Date])>=$B3),MATCH(Sprint_Info_leave[Start Date],Sprint_Info_leave[Start Date],0),"")+1,1),"") - this was only returning when the date is within Sprint 1.

Sprint Info is the table, where there is Sprint No, Sprint Start Date and Sprint End date
B3 had the leave start date of an employee.

Emp. Name	Start Date	End Date
ABC1	3-Aug-20​	7-Aug-20​
ABC2	27-Jul-20​	28-Jul-20​
ABC3	3-Jul-20​	3-Jul-20​
ABC	3-Jul-20​	6-Jul-20​
ABC2	3-Jul-20​	3-Jul-20​
ABC3	17-Jul-20​	20-Jul-20​

 

[skjohn]

Return a value from a column, if date is between dates

Hello there, I have a sheet with 3 columns. A, B and C I want a formula that I can type a start date and an end date, and it will return the Box number in Column C. For example if I typed 10-Apr-2017 in E5 and 10-Nov-2018 in F5, starting in H5 and going down by rows I would get 46,52,56 and...

www.mrexcel.com

I could solve the issue using the concepts in this thread- Thank you Fluff.. your solution is very effective and viable.