Formula to get data from a column based on a date range

[Nowherefast]

Hello:

I've been trying to create a formula to get the following info but have not had much luck.

I have 2 files and I need to populate the promo price from one file to another based on matching of the item number and then date range
See example below

Item Sale Date Get Promo Price Item Start Date End Date Promo Price 1123 4/1/2018 15 1123 1/1/2018 3/1/2018 5 5665 6/15/2018 null 1123 3/15/2018 3/31/2018 10 1123 4/1/2018 6/1/2018 15 5665 1/1/2018 5/1/2018 3.99 5665 7/1/2018 9/1/2018 7.99 <colgroup><col><col><col><col span="2"><col span="2"><col><col></colgroup><tbody> </tbody>
So I need to populate "Get promo price". In the example, for item 1123, it would be $15 and then for ITem 5665 it would be "null" Hope I make sense? Thank you.

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

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

 

[DRSteele]

This array formula might serve your purposes.

Book1
	A	B	C	D	E	F	G	H
1	Item	Sale Date	Get Promo Price		Item	Start Date	End Date	Promo Price
2	1123	4/01/18	15		1123	1/01/18	3/01/18	5
3	5665	6/15/18	0		1123	3/15/18	3/31/18	10
4					1123	4/01/18	6/01/18	15
5					5665	1/01/18	5/01/18	3.99
6					5665	7/01/18	9/01/18	7.99
Sheet32

Cell Formulas
Range	Formula
C2	{=MAX((A2=$E$2:$E$6)*(B2>=$F$2:$F$6)*(B2<=$G$2:$G$6)*$H$2:$H$6)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jtakw]

Hi,

Another way:

Book1
	A	B	C	D	E	F	G	H
1	Item	Sale Date	Get Promo Price		Item	Start Date	End Date	Promo Price
2	1123	4/1/2018	15		1123	1/1/2018	3/1/2018	5
3	5665	6/15/2018	0		1123	3/15/2018	3/31/2018	10
4					1123	4/1/2018	6/1/2018	15
5					5665	1/1/2018	5/1/2018	3.99
6					5665	7/1/2018	9/1/2018	7.99
Sheet243

Cell Formulas
Range	Formula
C2	=SUMPRODUCT((E$2:E$6=A2)*(F$2:F$6<=B2)*(G$2:G$6>=B2)*H$2:H$6)

 

[jtakw]

Also a SUMIFS option:

Book1
	A	B	C	D	E	F	G	H
1	Item	Sale Date	Get Promo Price		Item	Start Date	End Date	Promo Price
2	1123	4/1/2018	15		1123	1/1/2018	3/1/2018	5
3	5665	6/15/2018	0		1123	3/15/2018	3/31/2018	10
4					1123	4/1/2018	6/1/2018	15
5					5665	1/1/2018	5/1/2018	3.99
6					5665	7/1/2018	9/1/2018	7.99
Sheet243

Cell Formulas
Range	Formula
C2	=SUMIFS(H$2:H$6,E$2:E$6,A2,F$2:F$6,"<="&B2,G$2:G$6,">="&B2)

 

[Nowherefast]

Thank you all...All 3 worked perfectly.
Appreciate your time.

 

[jtakw]

You're welcome.

 

[DRSteele]

You're welcome.