Lookup value within a date range

[joelui]

hi guys,

I need some help with a formula, I have a value 8319 and date 02-Feb-18, I would like to the formula to return the result of column D, if it can find a match of Column A and also within the date range of Column B and C......

#	From Date	End Date	Result
8319	1-May-17	31-Dec-17	A
8319	1-Jan-18	10-May-18	B
8319	15-May-18	30-May-18	T

<tbody>
</tbody><colgroup><col><col span="2"><col></colgroup>

thanks in advance.
Cheers,
Joe

 

[Aladin Akyurek]

The outcome you expect for 8319 and 02-Feb-18 is B, right?

 

[joelui]

hi Aladin, that's correct. the outcome I expect is B

The outcome you expect for 8319 and 02-Feb-18 is B, right?

 

[Aladin Akyurek]

hi Aladin, that's correct. the outcome I expect is B

#	From Date	End Date	Result		8319	2-Feb-18	B
8319	1-May-17	31-Dec-17	A
8319	1-Jan-18	10-May-18	B
8319	15-May-18	30-May-18	T

<tbody>
</tbody>

F1: 8319

G1: 2-feb-18

In H1 control+shift+enter, not just enter:

=INDEX($D$2:$D$4,MATCH(F1,IF(G1>=B2:B4,IF(G1<=C2:C4,A2:A4)),0))

 

[joelui]

OMG, you are brilliant! thank you so much!

 

[Aladin Akyurek]

OMG, you are brilliant! thank you so much!

Glad to help.