If a date in a cell is between two dates in a table retrieve the right value/text

[nunomd]

Hi everyone,
I hope you could help me out with this question:
I would like to have the formula to insert in the cell F2, F3 and F4. If the date in cell E2 is between dates in A and B column should retrieve the right value/text in column C (to cell F2). The same for dates in cell E3, E4 and E5.
Thank you so much.

	A	B	C	D	E	F
1	From	To	PW		Dates
2	16/01/2015 <colgroup><col span="2"><col></colgroup><tbody> </tbody>	15/05/2015 <tbody> </tbody>	PW1		17/01/2015	PW1
3	16/05/2015 <tbody> </tbody>	31/05/2015 <tbody> </tbody>	PW2		18/01/2015	PW1
4					19/01/2015	PW1
5					19/05/2015	PW2

<tbody>
</tbody>

 

[jarjarbingie]

try:

F2 = OFFSET($C$1,MATCH($E2,$A$2:$A$8,1),0)

Note: Dates in column A should be in sorted in ascending order.

Hi everyone,
I hope you could help me out with this question:
I would like to have the formula to insert in the cell F2, F3 and F4. If the date in cell E2 is between dates in A and B column should retrieve the right value/text in column C (to cell F2). The same for dates in cell E3, E4 and E5.
Thank you so much.

	A	B	C	D	E	F
1	From	To	PW		Dates
2	16/01/2015 <tbody> </tbody>	15/05/2015 <tbody> </tbody>	PW1		17/01/2015	PW1
3	16/05/2015 <tbody> </tbody>	31/05/2015 <tbody> </tbody>	PW2		18/01/2015	PW1
4					19/01/2015	PW1
5					19/05/2015	PW2

<tbody>
</tbody>

 

[konew1]

Assuming that your dates are in ascending sequence, and that there are no gaps between the end date in column B and the next start date on next row in column A

=INDEX($C$3:$C$6,MATCH(E3,$A$3:$A$6,1))

 

[Trebor76]

Hi nunomd,

I've adjusted the ranges from the nifty main formula shown here to suit your needs as follows:

=INDEX($C$2:$C$3,SUMPRODUCT(--($E2>=$A$2:$A$3),--($E2<=$B$2:$B$3),ROW($A$1:$A$2)))

Copy and paste the above formula into cell F2 and copy down to cell F5.

Note with this formula the list does not have be sorted.

Regards,

Robert