If a date falls between two values, then put in the value from the corresponding column

[remobec]

OK, so I have a bunch of microfilm rolls storing records of old newspapers. Each roll is numbered, but we're missing some and I'd like to determine what we're missing.

Here is what my data looks like. A is the number of the microfilm roll, B is the start date of that roll, C is the end date of that roll.

A --- B --- C
8 --- 18-Dec-09 --- 18-Jun-10
9 --- 18-Jun-10 --- 13-Oct-11
10 --- 13-Oct-11 --- 18-Jun-12


I would like to be able to have a date, say June 20, 1910. Then it would look at the specific, determine that date falls in Roll 9 and return a "9." If there is not microfilm for a date, it can do an error message or whatever.

Is there a way to do this?

Thank you,
Becky

 

[Marcílio_Lobão]

remobec, Good afternoon.

Some doubts.....

a) Is your data at column B always in ASCENDING order?

b) Can the dates at column B be repeated?

Probably VLOOKUP or an INDEX + MATCH can help you.

We will wait for you answer.

 

[remobec]

Generally, data in Column B is always in Ascending order and the dates in Column B are not repeated. There are a few weird outliers, but I can leave those out.

 

[Marcílio_Lobão]

remobec, Good afternoon.

Using your information as basis:

A2:A5 --> Roll numbers
B2:B5 --> START DATE
C2:C5 --> END DATE

Try to do:

E3 --> Date for looking

F3 --> formula -->

=IF(COUNTIFS(B2:B5,"<="&E3,C2:C5,">="&E3)=0,"NOT found", SUMPRODUCT((B2:B5<=E3)*(C2:C5>=E3)*(A2:A5)))

Attention:
Your example contains some end dates that match start dates.
This can not happen because it breaks the integrity of the data.

I made a small example for you.

I modified the final dates for two rolls of film.
I colored the end date of roll 10 and included roll 11 to show that there is a space of time where there is no film.
I imagine that is exactly what you are looking for.
If the searched date falls in a range of these the message will warn.

https://www.sendspace.com/file/59t16i

Is that what you want?

I hope it helps.

 

[remobec]

Marcilio, that worked perfectly. Thank you!

 

[Marcílio_Lobão]

remobec, Good evening.

Glad to have helped you.

Thanks for the feedback.