formula/macro for multiple match lookup

[Vazilisk]

Hi guys,

I am hoping someone can help me get past this issue that i am stuck on. Going by the 2nd table below, i need to verify if the product# in the 2nd table matches the product# in the 1st table. If yes, then check if the "Sale Date" in table 2 falls within the range of Start & End date in row 2 of table 1 or row 3 of table 1. Finally, grab the corresponding price if the product matches and the sale date falls within the first or 2nd row. Any help is appreciated. Either a formula or a macro would help.

Example: In table 2, since the 1st product number matches in table 1, and the sale date meets the criteria of 3rd row in table 1, grab price $50.71.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Product#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD]Start Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]End Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Tier 1 Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]12345[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"]9/9/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl70, align: right"]12/31/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl70, align: right"]$80.72[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]12345[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"]9/3/2010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl70, align: right"]9/8/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl70, align: right"]$50.71[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]67890[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"]9/9/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl70, align: right"]12/31/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl70, align: right"]$22.20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]67890[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"]1/1/2006[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl70, align: right"]9/8/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl70, align: right"]$30.25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product#[/TD]
[TD]Sale Date[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]12345[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"]9/1/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]12345[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"]6/12/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]12345[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"]1/6/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]67890[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl70, align: right"]5/30/2014

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[CodeNinja]

Assuming your data above is in range A1:D11... In C8, enter =(SUMPRODUCT(($A$2:$A$5=A8)*(B8>=$B$2:$B$5)*(B8<=$C$2:$C$5)*($D$2:$D$5))) and copy down

Good luck,

CN.

 

[Vazilisk]

Thanks CN, can the formula be adjusted if the 2nd table had 500 rows in no particular order . Ideally if possible, rather than equal to cell A8, can it refer to the entire column A:A? =(SUMPRODUCT(($A$2:$A$5=A8)*(B8>=$B$2:$B$5)*(B8<=$C$2:$C$5)*($D$2:$D$5)))

 

[Vazilisk]

Thanks CN, can the formula be adjusted if the 2nd table had 500 rows in no particular order . Ideally if possible, rather than equal to cell A8, can it refer to the entire column A:A? =(SUMPRODUCT(($A$2:$A$5=A8)*(B8>=$B$2:$B$5)*(B8<=$C$2:$C$5)*($D$2:$D$5)))

I forgot to add. The 2nd table is in a different sheet.

 

[CodeNinja]

Of course... something like:

=(SUMPRODUCT(('Sheet1'!$A$2:$A$500='Sheet2'!A2)*('Sheet2'!B2>='Sheet1'!$B$2:$B$500)*('Sheet2'!B2<='Sheet1'!$C$2:$C$500)*('Sheet1'!$D$2:$D$500)))

Assuming sheet1 has the first table and it's range is A1:D500 and sheet2 has the second table and it's range is A1:C?
If not, adjust the sheet names and the reference ranges accordingly.


Good luck,

CN.

 

[Vazilisk]

Really appreciate the help! That was exactly what i needed.