I have this scenario:

Product_ID Product_Price Starting_Week
154 10 1
6919 15 32
6919 16 34
154 12 33
6919 17 36

Given a ProductID and a Week I need to return the price for that Product_ID
in that specific week or if the specific week doesn't exist I must return the
price for the closest week.
Per Examples:
- If ProductID=6919 and Week= 33 I must get the price for the ProductID=6919
Week=32 (15)
- If ProductID=6919 and Week=1 I must get the price for ProductID=6919 Week=32
- If ProductID=154 and Week= 3 I must get the price for ProductID=154 Week=1
- If ProductID=154 and Week=35 I must get the price for ProductID=154 Week=33

I have tryed with:
- Vlookup (adding a index column with Product_ID&Starting_Week)
- INDEX/MATCH
- Array Formula with INDEX/MATCH

and nothing is working... I really need you help with this... please

I will appreciate your help

Miko

Hi Miko,

If you intended to get the highest week number which is greater or equal to
the starting weeks for a given product, then:

Assume that your Product/Price/Starting Week-List is in cells A2:C7 (insert
an additional entry with price 15 and starting week 1 for product 6919), then
write your search product number into E2 and your search week into F2.

G2 will give you the correct starting week and should read:
=MAX(IF((E2=A2:A7)*(F2=C2:C7),C2:C7))
[enter as array formula with CTRL+SHIFT+ENTER]

H2 will give you the correct prive and should read:
=MAX(IF((E2=A2:A7)*(F2=C2:C7),B2:B7))
[enter as array formula with CTRL+SHIFT+ENTER]

HTH,
Bernd

Thanks Bernd... I was trying to write just one formula... As people say... "2
fresh eyes and mind always help!!"... your solution is perfect... in reality
I don't need to add the extra row... if the formula return 0 when the
appropiate product/week don't exist it is fine.

Thanks a bunch!!

Miko

"Bernd Plumhoff" wrote:

Hi Miko,

If you intended to get the highest week number which is greater or equal to
the starting weeks for a given product, then:

Assume that your Product/Price/Starting Week-List is in cells A2:C7 (insert
an additional entry with price 15 and starting week 1 for product 6919), then
write your search product number into E2 and your search week into F2.

G2 will give you the correct starting week and should read:
=MAX(IF((E2=A2:A7)*(F2=C2:C7),C2:C7))
[enter as array formula with CTRL+SHIFT+ENTER]

H2 will give you the correct prive and should read:
=MAX(IF((E2=A2:A7)*(F2=C2:C7),B2:B7))
[enter as array formula with CTRL+SHIFT+ENTER]

HTH,
Bernd

Hi,

The following formula should do the job, even though not as elegantly as you
want it to.

If the data are in columns A, B, and C, in the order shown in your sample
table, with headers in Row 1, and if E2 and F2 will contain the seach
criteria (i.e., the Product_ID and Starting_Week) for G2 to show the output
(price), try the following formula.
=IF(SUMPRODUCT(--(A2:A1000=E2)*--(C2:C1000=F2))=0,"Week_doesn't_match",SUMPRODUCT(--(A2:A1000=E2),--(C2:C1000=F2)*B2:B1000))

Modify the '1000's in the formula to suit to your data range.

If the week number you are inputting doesn't find a match in the data table,
the formula would return "Week_doesn't_match", so thAT you can modify it
(the formula doesn't automatically search for the closest week, as you
wanted; it has to be manually done)

For easy idenfication of ALL the Starting_Week number (Column C) for a given
Product_ID (E2), you could use conditional formatting for column C and format
the font-style, font-color, cell shading, etc., etc; the conditional format
formula in C2 will be,
=$E$2 = $A2, and use the conditional format painter (the little brush icon,
if installed) to the rest of the rows in column C.

Regards,
B. R. Ramachandran

"Miko" wrote:

I have this scenario:

Product_ID Product_Price Starting_Week
154 10 1
6919 15 32
6919 16 34
154 12 33
6919 17 36

Given a ProductID and a Week I need to return the price for that Product_ID
in that specific week or if the specific week doesn't exist I must return the
price for the closest week.
Per Examples:
- If ProductID=6919 and Week= 33 I must get the price for the ProductID=6919
Week=32 (15)
- If ProductID=6919 and Week=1 I must get the price for ProductID=6919 Week=32
- If ProductID=154 and Week= 3 I must get the price for ProductID=154 Week=1
- If ProductID=154 and Week=35 I must get the price for ProductID=154 Week=33

I have tryed with:
- Vlookup (adding a index column with Product_ID&Starting_Week)
- INDEX/MATCH
- Array Formula with INDEX/MATCH

and nothing is working... I really need you help with this... please

I will appreciate your help

Miko

Thanks B.R

It is not exactly what I was looking for but your input is appreciated...
matter of fact your suggestion just gave me new ideas :-))

Miko

"B. R.Ramachandran" wrote:

Hi,

The following formula should do the job, even though not as elegantly as you
want it to.

If the data are in columns A, B, and C, in the order shown in your sample
table, with headers in Row 1, and if E2 and F2 will contain the seach
criteria (i.e., the Product_ID and Starting_Week) for G2 to show the output
(price), try the following formula.
=IF(SUMPRODUCT(--(A2:A1000=E2)*--(C2:C1000=F2))=0,"Week_doesn't_match",SUMPRODUCT(--(A2:A1000=E2),--(C2:C1000=F2)*B2:B1000))

Modify the '1000's in the formula to suit to your data range.

If the week number you are inputting doesn't find a match in the data table,
the formula would return "Week_doesn't_match", so thAT you can modify it
(the formula doesn't automatically search for the closest week, as you
wanted; it has to be manually done)

For easy idenfication of ALL the Starting_Week number (Column C) for a given
Product_ID (E2), you could use conditional formatting for column C and format
the font-style, font-color, cell shading, etc., etc; the conditional format
formula in C2 will be,
=$E$2 = $A2, and use the conditional format painter (the little brush icon,
if installed) to the rest of the rows in column C.

Regards,
B. R. Ramachandran

"Miko" wrote:

I have this scenario:

Product_ID Product_Price Starting_Week
154 10 1
6919 15 32
6919 16 34
154 12 33
6919 17 36

Given a ProductID and a Week I need to return the price for that Product_ID
in that specific week or if the specific week doesn't exist I must return the
price for the closest week.
Per Examples:
- If ProductID=6919 and Week= 33 I must get the price for the ProductID=6919
Week=32 (15)
- If ProductID=6919 and Week=1 I must get the price for ProductID=6919 Week=32
- If ProductID=154 and Week= 3 I must get the price for ProductID=154 Week=1
- If ProductID=154 and Week=35 I must get the price for ProductID=154 Week=33

I have tryed with:
- Vlookup (adding a index column with Product_ID&Starting_Week)
- INDEX/MATCH
- Array Formula with INDEX/MATCH

and nothing is working... I really need you help with this... please

I will appreciate your help

Miko