Look Up Value with 2 Criteria and an IF Statement

[drummerboy1023]

May be a simple answer that I may be making more complex... I am trying to lookup a value in one tab and return it in another against multiple criteria...

the first tab looks like..

<table x:str="" style="border-collapse: collapse; width: 226pt;" width="301" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="2" width="64"> <col style="width: 63pt;" width="84"> <col style="width: 67pt;" width="89"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td> <td class="xl25" style="width: 48pt;" width="64">Date</td> <td class="xl25" style="width: 63pt;" width="84">End of Month</td> <td class="xl25" style="width: 67pt;" width="89">Start of Month</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">
</td> <td class="xl26" x:num="39896" x:fmla="=TODAY()">3/24/2009</td> <td class="xl26" x:num="39903" x:fmla="=EOMONTH(B2,0)">3/31/2009</td> <td class="xl24" style="width: 67pt;" x:num="39873" x:fmla="=DATE(YEAR(TODAY()),MONTH(TODAY()),1)" width="89">3/1/09</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">
Store</td> <td class="xl25">Location</td> <td class="xl25">Packages</td> <td class="xl25">Rate</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="" height="17">1</td> <td class="xl25">NE</td> <td class="xl25">12
</td> <td class="xl25" x:num="">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="" height="17">2</td> <td class="xl25">SW</td> <td class="xl25">
</td> <td class="xl25" x:num="">13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="" height="17">3</td> <td class="xl25">N</td> <td class="xl25">
</td> <td class="xl25" x:num="">14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="" height="17">4</td> <td class="xl25">S</td> <td class="xl25">
</td> <td class="xl25" x:num="">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="" height="17">5</td> <td class="xl25">E</td> <td class="xl25">
</td> <td class="xl25" x:num="">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="" height="17">6</td> <td class="xl25">W</td> <td class="xl25">
</td> <td class="xl25" x:num="">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="" height="17">7</td> <td class="xl25">N</td> <td class="xl25">
</td> <td class="xl25" x:num="">5</td> </tr> </tbody></table>
The second tab looks like...

<table x:str="" style="border-collapse: collapse;" width="493" border="0" cellpadding="0" cellspacing="0" height="187"><col style="width: 48pt;" span="2" width="64"> <col style="width: 63pt;" width="84"> <col style="width: 67pt;" width="89"> <col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td> <td colspan="2" class="xl26" style="border-right: 0.5pt solid black; width: 111pt;" x:num="39858" width="148"> 2/14/2009</td> <td colspan="2" class="xl26" style="border-left: medium none; border-right: 0.5pt solid black; width: 115pt;" x:num="39888" width="153"> 3/16/2009</td> <td colspan="2" class="xl26" style="border-left: medium none; border-right: 0.5pt solid black; width: 96pt;" x:num="39918" width="128"> 4/15/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl31" style="height: 12.75pt;" height="17">
</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">Packages</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">Cartons</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">Packages</td> <td class="xl36" style="border-top: medium none; border-left: medium none;">Cartons</td> <td class="xl37" style="border-top: medium none;">Packages</td> <td class="xl36" style="border-top: medium none;">Cartons</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" height="17">Store</td> <td class="xl29">
</td> <td class="xl35" style="border-left: medium none;">
</td> <td class="xl35" style="border-left: medium none;">
</td> <td class="xl35" style="border-left: medium none;">
</td> <td class="xl25">
</td> <td class="xl34">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" x:num="" height="17">1</td> <td class="xl30" x:num="">10</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=B4*2">20</td> <td class="xl34" style="border-left: medium none;" x:num="">12</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=D4*2">24</td> <td class="xl24" x:num="">15</td> <td class="xl34" x:num="" x:fmla="=F4*2">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" x:num="" height="17">2</td> <td class="xl30" x:num="">5</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=B5*2">10</td> <td class="xl34" style="border-left: medium none;" x:num="">15</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=D5*2">30</td> <td class="xl24" x:num="">12</td> <td class="xl34" x:num="" x:fmla="=F5*2">24</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" x:num="" height="17">3</td> <td class="xl30" x:num="">8</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=B6*2">16</td> <td class="xl34" style="border-left: medium none;" x:num="">4</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=D6*2">8</td> <td class="xl24" x:num="">14</td> <td class="xl34" x:num="" x:fmla="=F6*2">28</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" x:num="" height="17">4</td> <td class="xl30" x:num="">4</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=B7*2">8</td> <td class="xl34" style="border-left: medium none;" x:num="">8</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=D7*2">16</td> <td class="xl24" x:num="">6</td> <td class="xl34" x:num="" x:fmla="=F7*2">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" x:num="" height="17">5</td> <td class="xl30" x:num="">6</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=B8*2">12</td> <td class="xl34" style="border-left: medium none;" x:num="">6</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=D8*2">12</td> <td class="xl24" x:num="">8</td> <td class="xl34" x:num="" x:fmla="=F8*2">16</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" x:num="" height="17">6</td> <td class="xl30" x:num="">3</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=B9*2">6</td> <td class="xl34" style="border-left: medium none;" x:num="">10</td> <td class="xl34" style="border-left: medium none;" x:num="" x:fmla="=D9*2">20</td> <td class="xl24" x:num="">13</td> <td class="xl34" x:num="" x:fmla="=F9*2">26</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl31" style="height: 12.75pt;" x:num="" height="17">7</td> <td class="xl32" style="border-left: medium none;" x:num="">4</td> <td class="xl32" style="border-left: medium none;" x:num="" x:fmla="=B10*2">8</td> <td class="xl32" style="border-left: medium none;" x:num="">6</td> <td class="xl32" style="border-left: medium none;" x:num="" x:fmla="=D10*2">12</td> <td class="xl33" x:num="">10</td> <td class="xl32" x:num="" x:fmla="=F10*2">20</td> </tr> </tbody></table>
Now what I am trying to do is find the the column (3/16/2009 - this cell is merged between two columns) in the second range that is within the End of Month (3/31/2009) and Start of Month Range (3/1/2009). I then need for it to return the # of packages for the corresponding store in the packages column in the first tab.. (i.e. 12 for Store 1.)

I have been trying to nest hlookups, vlookups, index, and match formulas in various ways but always hit a roadblock. The reason being is because every time I open the file the Date will change as it is a Today() function and the End of Month and Start of Month will change accordingly.

Thanks for the help!... starting to think this may not be possible....

 

[DonkeyOte]

There are a few ways to do this... one would be to use Sumproduct

If we assume source data of:

Excel Workbook
	A	B	C	D	E	F	G
1		14/02/2009		16/03/2009		15/04/2009
2		Packages	Cartons	Packages	Cartons	Packages	Cartons
3	Store
4	1	10	20	12	24	15	30
5	2	5	10	15	30	12	24
6	3	8	16	4	8	14	28
7	4	4	8	8	16	6	12
8	5	6	12	6	12	8	16
9	6	3	6	10	20	13	26
10	7	4	8	6	12	10	20
Sheet2

<span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

We can have results of:

Excel Workbook
	A	B	C	D
1		Date	End of Month	Start of Month
2		24/03/2009	31/03/2009	01/03/2009
3	Store	Location	Packages	Rate
4	1	NE	12	10
5	2	SW	15	13
6	3	N	4	14
7	4	S	8	8
8	5	E	6	6
9	6	W	10	10
10	7	N	6	5
Sheet1

 

[drummerboy1023]

Thanks for the quick response... I entered the formula exactly as how you typed it and yet got a 0 value...

suggestions on where I may have went wrong or on another way I could attack the problem.

Thanks again!