checking if a date is in between a certain range. with multiple ranges

[biddum]

I have an excel worksheet with the begin dates and end dates of the semester in a college. The sheet holds data from the year 2012 to 2018.
I need help solving when a user enters in a date, to figure out the end date if that semester. If it not in that range, state that the school is closed.

For example, I type in 11/2/2012. It should state the end date is 12/8/2012.
If i typed in 4/4/2012, it would state that school is closed.
If I typed in 11/2/2014 it would state the end date is 12/7/2012.

I attached the worksheet to see if anyone can solve me problem, I feel that I am close but not quite there yet...



semester_ends.xlsx

Thank You!

[Ron Coderre]

Using your posted workbook, this regular formula returns the end date of the semester that contains the K4 date...or "School is closed" if the date is not included in a semester

Please Login or Register  to view this content.

Is that something you can work with?

[biddum]

Wow thank you! works like a charm! can you explain though how the MATCH and INDEX formula is used to make this work? Thank you

[Pete_UK]

Put this in M4:

=IF(K4<A3,"too early",IF(INDEX($B$3:$B$14,MATCH(K4,$A$3:$A$14))>=K4,INDEX($B$3:$B$14,MATCH(K4,$A$3:$A$14)),"Closed"))

Hope this helps.

Pete

EDIT: Ah, I see you've already received a solution.

[Ron Coderre]

Wow thank you! works like a charm! can you explain though how the MATCH and INDEX formula is used to make this work? Thank you

Regarding this part of the formula I posted: MATCH(1,INDEX((K4>=A3:A14)*(K4<=B3:B14),0),0)
This part: (K4>=A3:A14)*(K4<=B3:B14)
returns a series of 1's and 0's...There should only be one instance or no instances of a 1.

The MATCH function is looking for that 1...but, structured like this: (K4>=A3:A14)*(K4<=B3:B14)
Excel can only process the 1's and 0's if the series is flagged as an ARRAY. You would typically do that by completing the formula by holding CTRL and SHIFT when you press ENTER (to create an array formula). However, if you simply enclose that section within an INDEX function, no C+S+E is required.

Does that help?

[biddum]

yes it does! thank you

[Ron Coderre]

Glad to help.