Formula to fetch value based on date range

[Muralidaran]

Hi,
Appreciate your help fetching a value based on the date entered by user, I have tried functions like sumifs and index and multiple matches however couldn't complete as expected.
XLookup function is not available in my version.

 

[Skybluekid]

Could you let us know what version(s) you currently have?

 

[Skybluekid]

Try the below:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Date	Legend	Value		Date	Legend	Value
3		28-Jan	MP-RT	24		01-Feb	MP-RT	24
4		28-Jan	MP-OT	54		01-Feb	MP-OT	54
5		01-Mar	MP-RT	34		01-Apr	MP-RT	34
6		01-Mar	MP-OT	64		08-Apr	MP-OT	64
7		01-Oct	MP-RT	44		01-Nov	MP-RT	44
8		01-Oct	MP-OT	74		01-Nov	MP-OT	74
9
Sheet1

Cell Formulas
Range	Formula
H3:H8	H3	=INDEX($D$3:$D$8,AGGREGATE(15,6,(ROW($B$3:$B$8)-ROW($B$2))/(($B$3:$B$8<F3)*($C$3:$C$8=G3)),COUNTIF($C$3:C3,G3)))

 

[Muralidaran]

Try the below:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Date	Legend	Value		Date	Legend	Value
3		28-Jan	MP-RT	24		01-Feb	MP-RT	24
4		28-Jan	MP-OT	54		01-Feb	MP-OT	54
5		01-Mar	MP-RT	34		01-Apr	MP-RT	34
6		01-Mar	MP-OT	64		08-Apr	MP-OT	64
7		01-Oct	MP-RT	44		01-Nov	MP-RT	44
8		01-Oct	MP-OT	74		01-Nov	MP-OT	74
9
Sheet1

Cell Formulas
Range	Formula
H3:H8	H3	=INDEX($D$3:$D$8,AGGREGATE(15,6,(ROW($B$3:$B$8)-ROW($B$2))/(($B$3:$B$8<F3)*($C$3:$C$8=G3)),COUNTIF($C$3:C3,G3)))

Thanks for the help, its working.

 

[Skybluekid]

You are very welcome