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


[TABLE="width: 650"]
<colgroup><col><col><col><col span="2"><col span="2"><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 650"]
<colgroup><col><col><col><col span="2"><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD]Sale Date[/TD]
[TD]Get Promo Price[/TD]
[TD][/TD]
[TD]Item[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Promo Price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1123[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD] 15[/TD]
[TD][/TD]
[TD="align: right"]1123[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5665[/TD]
[TD="align: right"] 6/15/2018[/TD]
[TD] null[/TD]
[TD][/TD]
[TD="align: right"]1123[/TD]
[TD="align: right"]3/15/2018[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1123[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5665[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]3.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5665[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]9/1/2018[/TD]
[TD="align: right"]7.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 650"]
<colgroup><col><col><col><col span="2"><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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