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
|