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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]PW[/TD]
[TD][/TD]
[TD]Dates[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 187"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]16/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD]15/05/2015
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PW1[/TD]
[TD][/TD]
[TD]17/01/2015[/TD]
[TD]PW1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD]16/05/2015
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD]31/05/2015
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PW2[/TD]
[TD][/TD]
[TD]18/01/2015[/TD]
[TD]PW1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/01/2015[/TD]
[TD]PW1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/05/2015[/TD]
[TD]PW2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]PW[/TD]
[TD][/TD]
[TD]Dates[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD]16/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD]15/05/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PW1[/TD]
[TD][/TD]
[TD]17/01/2015[/TD]
[TD]PW1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD]16/05/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD]31/05/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PW2[/TD]
[TD][/TD]
[TD]18/01/2015[/TD]
[TD]PW1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/01/2015[/TD]
[TD]PW1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/05/2015[/TD]
[TD]PW2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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