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?

 

[RonB1111]

You can use VLookup for the Sheet 1 Price formula if you add 3 columns to Sheet 2:
A new Col A with the formula: =B2&F2
Col B is now SKU
Col C is now Price
Col D is now Effective Date
Col E is the "Next Qtr Start Date" with the formula: =IF(MONTH(D2)<=3,DATE(YEAR(D2),4,1),IF(MONTH(D2)<=6,DATE(YEAR(D2),7,1),IF(MONTH(D2)<=9,DATE(YEAR(D2),10,1),DATE(YEAR(D2)+1,1,1))))
Col F is the "Useable Next Qtr Start Date" with the formula: =IF(OR(B2<>B3,E2<>E3),E2,"Don't Use")

You'll have to 1st sort Sheet 2 by Col B SKU (primary) and Col D Eff Date (secondary)
The formula on Sheet 1 for price would be:
=vlookup(A2&B2,Sheet2!A:F,3,false)

I assumed your starting date is always the 1st day of a quarter based on your 2nd last paragraph. If the starting date can be any date, then my approach above would not work.

Also, you could set up autofilters on Sheet 2 and then filter by Col F for any specific quarter.

 

[xpous]

Hi Ron for your reply and help.

Here is the thing. The items in Sheet 1 are not sorted. Do I need to do a sort so that the formulas will work?

I don't get why have the "Next Qtr Start Date". Can you explain?

For some SKU products, the Starting Date is the first day of the quarter and for some is 45 days after the 1st day of the quarter.

 

[mgirvin]

Maybe:

Excel Workbook
	A	B	C
1	SKU	Price	Effective Date
2	45-6	$16.00	12/31/2011
3	45-6	$17.00	12/30/2011
4	45-7	$22.00	1/1/2012
5	45-8	$21.00	12/22/2011
6	45-9	$30.00	12/2/2011
7	45-9	$75.00	12/15/2011
8	45-6	$16.50	1/2/2012
9	45-6	$14.00	2/2/2012
Sheet2

Excel Workbook
	A	B	C	D
1	SKU	Starting Date	Quarter	Price
2	45-6	1/1/2012	Q1	16
3	45-7	1/2/2012	Q1	22
4	45-8	1/3/2012	Q1	21
5	45-9	1/4/2012	Q1	75
Sheet1

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


There is probably a more elegant way than the brute force formula I created...

 

[xpous]

I have been for the past 2 hours trying to fit the example you posted but in the actual Excel file without any luck. The result of the prices were not the ones expected. It was showing price values but not the correct ones. Can you test your approach here in this Example file I have created? This have the same sheet naming, column structure and SKU values.

http://dl.dropbox.com/u/1288821/Dashboard.xlsx

 

[mgirvin]

I am not sure what you want to do with that posted spreadsheet.

Does this help:

<TABLE style="WIDTH: 243pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=325><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #002060; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 height=35 width=64>SKU2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 50pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=67>Price2</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 62pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl67 width=83>EffectiveDate</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=47></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-6</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=67>$16.00 </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=83>12/31/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-6</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=67>$17.00 </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=83>12/30/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-7</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=67>$22.00 </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=83>1/1/2012</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-8</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=67>$21.00 </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=83>12/22/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-9</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=67>$30.00 </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=83>12/2/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-9</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=67>$75.00 </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=83>12/15/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-6</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl65 width=67>$16.50 </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl68 width=83>1/2/2012</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 height=21 width=64>45-6</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl70 width=67>$14.00 </TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl71 width=83>2/2/2012</TD>


<TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD>
</TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=21></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #002060; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 height=35 width=64>SKU1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 50pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" id=td_post_2965676 class=xl63 width=67>StartingDate</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 62pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl63 width=83>Quarter</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #002060; WIDTH: 35pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl67 width=47>Price1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-6</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=67>1/1/2012</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=83>Q1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=47>16</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-7</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=67>1/2/2012</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=83>Q1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=47>22</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 height=21 width=64>45-8</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl66 width=67>1/3/2012</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl64 width=83>Q1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=47>21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 height=21 width=64>45-9</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 50pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl73 width=67>1/4/2012</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #efefef; WIDTH: 62pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl69 width=83>Q1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #ccffcc; WIDTH: 35pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=47>75</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>

Formula in D12 (first green):

=INDEX(Price2,SUM(IF(SKU2_=A12,IF(EffectiveDate=MAX(IF(SKU2_=A12,IF(EffectiveDate<=B12,EffectiveDate))),ROW(EffectiveDate)-ROW($C$2)+1))))

Enter with keystrokes Ctrl + Shift + Enter.

 

[xpous]

Can you try doing the formula in the Excel file I have provided? I keep having the same issue with the formula you gave. The price value is not the correct one. I am getting $7881.75 when it is suppose to be $577.74. It is taking the 2 value of the index from the price list and not the price value that it should which is almost on the end of the list.

 

[mgirvin]

Looking at your spreadsheet, I cannot make heads or tails. The original description you posted made sense, but when I try to find the start date and sku numbers in the same table in the spreadsheet, I can't. May try to describe the spreadsheet as well as you did the original question and I can take a look.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[xpous]

Hi Mike:

I put some colors in the columns to help identify the columns that are related.

Sheet 1 = Manifest
Sheet 2 = Prices

The Sheet call Rules is only used to determine what is the "Period Cut" date from the Manifest sheet. The "Period Cut" field is the "Starting Date" of the Quarter. By "Period Cut" it means that the Effective Date should be equal or smaller than the "Period Cut" date but, it should be the closest date to the "Period Cut". If, for example, the same SKU is listed in the "Prices" sheet multiple times with different Effective dates, it should bring the Price value of the highest date that doesn't go over the Period Cut date.

The SKU Long, Product Description, Brand, Discount, and other columns may not be of your need.

Let me know if I have explained it better.

 

[RonB1111]

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.