Matching on multiple criteria with a date range lookup

[BILLYBOB1972]

I'm ok with index match formulas but I'm stuck with working out how to use a match with multiple criteria and finding a value based on a date range.

In the example below I need to match the reference but also find the price based on the date. The value in cells c2 & c3 should return 5.5 and cell c4 should return 5.75.

Is this possible. I've spent some time looking through multiple sites for assistance but I can't find anything that works. This may not even be a n index match so don't know if I'm even asking the right question. Hoping someone can point me in the right direction.

Reference (Column A)	Date (Column B)	Price (Column C)		Reference (Column E)	From (Column F)	To (Column G)	Price (Column H)
abc	01/04/2020			ABC	01/04/2019	11/04/2020	5.5
abc	10/04/2020			ABC	12/04/2020		5.75
abc	19/04/2020			DEF	01/01/2019		8

 

[Toadstool]

Hi BillyBob1972,

Does this do what you want?

BillyBob1972.xlsx
	A	B	C	D	E	F	G	H
1	Reference (Column A)	Date (Column B)	Price (Column C)		Reference (Column E)	From (Column F)	To (Column G)	Price (Column H)
2	abc	01-Apr-20	5.5		ABC	01-Apr-19	11-Apr-20	5.5
3	abc	10-Apr-20	5.5		ABC	12-Apr-20		5.75
4	abc	19-Apr-20	5.75		DEF	01-Jan-19		8
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=INDEX($H$2:$H$6,MATCH(1,INDEX(($E$2:$E$6=A2)*(($F$2:$F$6<=B2)*(($G$2:$G$6>=B2)+($G$2:$G$6=""))),0,1),0))

 

[BILLYBOB1972]

Hi Toadstool,

Works perfect. Thanks for your help.