Complex VLookup

[xpous]

Hi:

I need some help with a Vlookup I am trying to create. I have 2 Sheets.

Sheet 1 have the following columns:
- SKU
- Starting Date
- Quarter
- Price

The Price column is the one I need to fill in with a formula.

Sheet 2 have the following columns:
- SKU
- Price
- Effective Date

The SKU is repeated in this list of Sheet 2 because the prices may change with a new Effective Date. The value of the price I need to have in Sheet 1 should be the last price change that is closest to the Starting Date of that Quarter but not greater than the Start Date. For example, if there are 4 prices for a single SKU and the Effective dates are 12/30/2011, 12/31/2011, 1/2/2012 and 2/2/2012 it should use the price of the 12/31/2011 for Quarter 1 2012 since that is the closest to the Starting Date which is 1/1/2012.

Do you have any idea how can this be created using VLookup, Index, or any other function?

 

[mgirvin]

Maybe in J2 on "Manifest" sheet:

=INDEX(Prices!$C$2:$C$108,SUM(IF(Prices!$B$2:$B$108=E2,IF(Prices!$A$2:$A$108=MAX(IF(Prices!$B$2:$B$108=E2,IF(Prices!$A$2:$A$108<=L2,Prices!$A$2:$A$108))),ROW(Prices!$A$2:$A$108)-ROW(Prices!$A$2)+1))))

and copy down.

But I think there is a problem with your SKU data. Many of the SKUs on the "Manifest" sheet are not in the Prices sheet.

If you named the columns, the formula would look like:

=INDEX(Prices,SUM(IF(SKU=E2,IF(EffectiveDate=MAX(IF(SKU=E2,IF(EffectiveDate=L2,EffectiveDate))),ROW(EffectiveDate)-ROW(Prices!$A$2)+1))))
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>Maybe that will work?</o>

 

[xpous]

Hi Ron:

Check the URL of the file. There I have a sheet with the rules and the rules are already creating the starting date of each quarter. I call it "Period cut" becuase, after that date, even when the price may change within that quarter, doesn't apply to it. Only if the price is changed before that date.

Sheet 1 does not need to be sorted. Sheet 2 does need to be sorted.

The reason for the Next Qtr Start Date column is to identify on Sheet 2 the quarter each price change applies - this makes the formula in Col F simpler which is to identify a SKU's last price change in the quarter. Col F then makes a vlookup on Sheet 1 possible by concatenating the SKU and Effective Date on Sheet 1 and matching it to the concatenated SKU and Col F on Sheet 2.

If some Starting Dates are the 1st of the quarter but some are 45 days into the quarter, the formula for Next Qtr Start Date needs to be modified based on the rules for using the 1st of the qtr versus 45 days into the qtr. If you can provide those rules I'll try to modify the formula.

 

[xpous]

Hi Mike:

Copying and Pasting your formula to the Manifest J2 cell is bringing the price of another product. It is basically bringing the 2nd record of the Prices list and not getting the right price based on the SKU number. This is what I was trying to explain yesterday and that's why I tought it was better to just add the Excel file to the forum so you can see.

If I copy down the function, it will bring prices but again, not the correct ones. Shouldn't the function have a Vlookup somewhere too?

Maybe in J2 on "Manifest" sheet:

=INDEX(Prices!$C$2:$C$108,SUM(IF(Prices!$B$2:$B$108=E2,IF(Prices!$A$2:$A$108=MAX(IF(Prices!$B$2:$B$108=E2,IF(Prices!$A$2:$A$108<=L2,Prices!$A$2:$A$108))),ROW(Prices!$A$2:$A$108)-ROW(Prices!$A$2)+1))))

and copy down.

But I think there is a problem with your SKU data. Many of the SKUs on the "Manifest" sheet are not in the Prices sheet.

If you named the columns, the formula would look like:

=INDEX(Prices,SUM(IF(SKU=E2,IF(EffectiveDate=MAX(IF(SKU=E2,IF(EffectiveDate=L2,EffectiveDate))),ROW(EffectiveDate)-ROW(Prices!$A$2)+1))))
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>Maybe that will work?</o>

 

[mgirvin]

Can you tell me what the first couple of expected results from the formula should be in the Price column?

 

[xpous]

The only one that should appear with a price is the first SKU and should say $30. Since all the prices (in the Price list) are effective December 3, 2011 and the records in the Manifest sheet have Q2, only the first record have a row in the Price list with the Effective date of 4/1/2011. If you change the C column in the Manifest sheet to have "Q1 2012" instead of "Q2 2011" for all the rows, it should have a price value for all SKUs.

 

[RonB1111]

Hi Ron:

Check the URL of the file. There I have a sheet with the rules and the rules are already creating the starting date of each quarter. I call it "Period cut" becuase, after that date, even when the price may change within that quarter, doesn't apply to it. Only if the price is changed before that date.

After looking at your workbook, I'm not going to be able to use the Rules worksheet in my formula to adjust for 1st of qtr or mid quarter starting date on Sheet 2 (Price Sheet). The rules appear to be Account based rather than SKU based and my formula used the latter.

At the moment I don't have a solution for this piece unless you can think of another way to express the rule for 1st vs mid qtr.

I'd like to mention that on your "Manifest" sheet, the Period Cut formula is creating the date as text rather than an excel date - just wanted you to be aware of this in case you try to use it in a math formula or try to match it to an excel date cell.

 

[xpous]

Hi Ron:

Thanks for the tip about the date. I will probably need the Period Cut date as a date format and not as a text so thanks for bringing that up. At least I know how to do that.

The thing about the rules of when the Quarter cuts off is based on Accounts and not SKU like you mentioned. That's why I have created some internal SKU's which combines the SKU and the Account. That way it is unique. That combined value is in a hidden column next to the SKU column and I have called it SKU Long. Maybe with that you can come up with a solution.

 

[mgirvin]

When I plug Sku numbers that exisit on the "Manifest" sheet into the Sku column on the "Prices" column, I get the formula to work. Try fixing your SKU numbers

"Manifest" sheet:

<TABLE style="WIDTH: 296pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=395><COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 34.15pt; mso-height-source: userset" height=45><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; WIDTH: 79pt; HEIGHT: 34.15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl79 height=45 width=105>SKU</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 140pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=187>Price</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=103>Period Cut</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77 height=17>59148064023</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl80 align=right>1010.11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>1/1/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77 height=17>59148064123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl80 align=right>3940.88</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>4/1/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl77 height=17>59418001215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl80 align=right>121.49</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>4/1/2011</TD></TR></TBODY></TABLE>


Formula in J2:

=INDEX(Prices!$C$2:$C$108,SUM(IF(Prices!$B$2:$B$108=E2,IF(Prices!$A$2:$A$108=MAX(IF(Prices!$B$2:$B$108=E2,IF(Prices!$A$2:$A$108<=L2,Prices!$A$2:$A$108))),ROW(Prices!$A$2:$A$108)-ROW(Prices!$A$2)+1))))

and data on "Prices" sheet:
<TABLE style="WIDTH: 247pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=328><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl66 height=40 width=90>Effective Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; WIDTH: 115pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl73 width=153>SKU</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8e4bc; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl72 width=85>Unit Price </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=17 align=right>12/31/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>59148064023</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 1,010.11 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/15/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>59148064123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 7,881.75 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>2/29/2012</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>59148064123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 3,940.88 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>59418001215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 7,881.75 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>3/31/2012</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>59418001215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 121.49 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>59418001215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 7,881.75 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003085522</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 7,881.75 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003085722</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 7,881.75 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003161112</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 764.55 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003161212</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 764.55 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003218710</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 542.55 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003256016</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 339.77 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003257016</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 678.28 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003362212</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 933.59 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17 align=right>12/3/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>00003362312</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>$ 942.49 </TD></TR></TBODY></TABLE>

 

[xpous]

Mike:

The SKU don't need to be fixed. Those are the correct numbers and they are well written in the Prices sheet and the Manifest sheet. Maybe it is working in your example because you have now in the Prices list 59148064023 as the first value. Try using the same order that is currently in the Excel file I provided since that is how the Prices will be extracted. The SKU 59148064023 is listed a couple of times and that will be the case. The formula should get the price that applies to that quarter based on the Period Cut date and should take the price closest to that date without going over.

 

[xpous]

The other important thing is that I can't do a sort in the Manifest sheet. The reason being that the SKU are repeated through the Accounts and Carriers and they should be grouped by Carrier and Account, not sorted by SKU. I can do a sort to the Prices sheet if it is needed. Just let me know what should be the sorting criteria.