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]

I thought that is what I did (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) - I must do not understand. For one last ditch attempt, can you make a small data set that simulates what you are trying to do. Do a small data set for the Manifest sheet and a small data set for the Prices sheet and give me what the expected results from the formula should be for the Manifest sheet. That way maybe I can back into the logic of what you are trying to do.

 

[xpous]

<TABLE style="WIDTH: 291pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=387><COLGROUP><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><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: 99pt; HEIGHT: 34.15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" id=td_post_2965959 class=xl75 height=45 width=132>SKU</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 width=82>Rebate Disc</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 width=82>Admin Fees</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8e4bc; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl76 width=91>Price</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=xl74 height=17>59148064023</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>13.5%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl78> $ 30.00 </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=xl74 height=17>59148064123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>13.5%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA</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=xl74 height=17>59418001215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA</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=xl74 height=17>59418001315</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA

</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=xl74 height=17>59418001665</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA</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=xl74 height=17>59148000813</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA</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=xl74 height=17>59148000835</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA</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=xl74 height=17>59148000913</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA</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=xl74 height=17>59148000935</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA</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=xl74 height=17>59148000613</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>NA</TD></TR></TBODY></TABLE>
This is how the result should look like if the Period Cut column says "4/1/2011" and the Quarter column is Q2 2011. If you change the Quarter Column to "Q1 2012" the values should be like this:

<TABLE style="WIDTH: 291pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=387><COLGROUP><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><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: 99pt; HEIGHT: 34.15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" id=td_post_2965959 class=xl77 height=45 width=132>SKU</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=82>Rebate Disc</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl71 width=82>Admin Fees</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8e4bc; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl78 width=91>Price</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=xl76 height=17>59148064023</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>13.5%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> $ 577.74 </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=xl76 height=17>59148064123</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>13.5%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> $ 577.74 </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=xl76 height=17>59418001215</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> NA </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=xl76 height=17>59418001315</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> NA

</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=xl76 height=17>59418001665</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> NA </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=xl76 height=17>59148000813</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> $ 485.40 </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=xl76 height=17>59148000835</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> $ 1,617.94 </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=xl76 height=17>59148000913</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> $ 485.40 </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=xl76 height=17>59148000935</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> $ 1,617.94 </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=xl76 height=17>59148000613</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75>11.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 align=right>3.0%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> $ 485.40 </TD></TR></TBODY></TABLE>

 

[xpous]

This is leaving the Prices list as it is. Those are the results I should expect depending on the Quarter.

 

[mgirvin]

As I mentioned earlier and RonB1111 mentioned, there are numeroud data mismatches (Numbers and Numbers Stored As Text).

So, let's try:

In J2 on "Manifest" Sheet:

=LOOKUP(9.99999999999999E+307,Prices!$C$2:$C$108/((Prices!$B$2:$B$108&""=E2&"")*(Prices!$A$2:$A$108=MAX((Prices!$B$2:$B$108&""=E2&"")*(Prices!$A$2:$A$108<=L2+0)*Prices!$A$2:$A$108))))

If we use these Defined Names:

ED =Prices!$A$2:$A$108<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
P =Prices!$C$2:$C$108<o></o>
SKU =Prices!$B$2:$B$108

<o>The formula is:</o>
<o></o>
=LOOKUP(9.99999999999999E+307,P/((SKU&""=E2&"")*(ED=MAX((SKU&""=E2&"")*(ED<=L2+0)*ED))))

 

[mgirvin]

If you want dynamic ranges (it looks like your template would get future Effective Dates, SKU and Prices), maybe create Dynamic Defined Names:

Picture of data and formulas cells (J column has formula):

Excel Workbook
	C	E	J	L
1	Quarter	SKU	Price	Period Cut
2	Q1 2012	59148064023	577.74	1/1/2012
3	Q1 2012	59148064123	577.74	1/1/2012
4	Q1 2012	59418001215	#N/A	1/1/2012
5	Q1 2012	59418001315	#N/A	1/1/2012
6	Q1 2012	59418001665	#N/A	1/1/2012
7	Q1 2012	59148000813	485.4	1/1/2012
8	Q1 2012	59148000835	1617.94	1/1/2012
9	Q1 2012	59148000913	485.4	1/1/2012
10	Q1 2012	59148000935	1617.94	1/1/2012
11	Q1 2012	59148000613	485.4	1/1/2012
Manifest

Formula and Defined Names in J2:

Excel Workbook
	J
2	577.74
Manifest

Cell Formulas
Range	Formula
J2	=LOOKUP(9.99999999999999E+307,P/((SKU&""=E2&"")*(ED=MAX((SKU&""=E2&"")*(ED<=L2+0)*ED))))

Excel Workbook
Name	Refers To
ED	=Prices!$A$2:INDEX(Prices!$A:$A,MATCH(9.99999999999999E+307,Prices!$A:$A))
P	=Prices!$C$2:INDEX(Prices!$C:$C,MATCH(9.99999999999999E+307,Prices!$C:$C))
SKU	=Prices!$B$2:INDEX(Prices!$B:$B,MATCH(9.99999999999999E+307,Prices!$B:$B))
Workbook Defined Names

BTW, this formula does not require Ctrl + Shift + Enter (I forgot to mention that in the last post).

 

[xpous]

What is the "9.99999999999999E+307"?? I've never seen that trick.

 

[mgirvin]

It is the biggest number in Excel.

Did the formula work?

 

[xpous]

Man you are a genius! That worked! Where can I donate some money to you? I am truly thankful for you help.

 

[mgirvin]

Cool! I am glad that it worked! It did take a while because the data was inconsistent.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
No Money, though. Since our job as humans is to make the world a better place and have fun, it’s all good!<o></o>

 

[xpous]

Well, I guess I can only say THANK YOU from the bottom of my heart... and Happy Holidays to both of you! Keep up the good work and sharing the knowledge.

Thanks again.