Three way lookup (1 row, two columns)

[vijayamani1987]

Here is my requirement...

	Oct-13	Nov-13	Dec-13
Item name	Cost	MRP	Qty	Cost	MRP	Qty	Cost	MRP	Qty
clip	20	25	3	25	30	5	18	30	6
stone clip	70	80	2	75	90	4	65	90	8
oval clip	60	72	1	60	75	0	60	80	2

<tbody>
</tbody>

I need to find the MRP value of stone clip for nov-13.

I am not much familiar with excel. Please give me a solution and thanks a lot in advance...

 

[Aladin Akyurek]

Hi Aladin Akyurek,
while using ur formula i got an error
G2=SUMPRODUCT(--(TEXT($A$2:$A$8,"mmmyy")=TEXT(G2,"mmmyy")),--($B$2:$B$8=G3),$C$2:$C$8,$D$2:$D$8)

I think this is bcoz in G2 my input is 1/02/2014, but i formated it to FEB 14.I am not clear that how do we compare the month and year of (A2:A8) = month and year of (G2)

If A2:A8 and G2 are true (not text) dates, TEXT transformation will succeed. In G2 one would expect a month/year specification using a first day date, like 1-Feb-14.

What error did you get? By the way, it's important to have the right formatting string: mmmyy stands for month and year. In French for example, this would be different.

 

[markmzz]

Hi Aladin Akyurek,
while using ur formula i got an error
G2=SUMPRODUCT(--(TEXT($A$2:$A$8,"mmmyy")=TEXT(G2,"mmmyy")),--($B$2:$B$8=G3),$C$2:$C$8,$D$2:$D$8)

I think this is bcoz in G2 my input is 1/02/2014, but i formated it to FEB 14.I am not clear that how do we compare the month and year of (A2:A8) = month and year of (G2)

Hi Vijayamani1987,

Did you try my formulas of the post #29?. Look at this:

DATE	NAME	MRP	QTY
11/02/2014	CLIP	5	5		DATE	fev 14
18/02/2014	CLIP	5	7		NAME	CLIP	Formulas
28/02/2014	RING	16	4		TOTAL	108	=SUM(INDEX((B2:B8=G3)*(A2:A8-DAY(A2:A8)=G2-DAY(G2))*C2:C8*D2:D8,))
03/04/2014	RING	14	2		TOTAL	108	=SUM((B2:B8=G3)*(A2:A8-DAY(A2:A8)=G2-DAY(G2))*C2:C8*D2:D8)
14/04/2014	RING	20	2
22/02/2014	CLIP	8	6
06/04/2014	CLIP	7	4
**********	******	******	****	**	*******	********	*******************************************************************

<tbody>
</tbody>

PS: fev is feb.

Markmzz

 

[Aladin Akyurek]

If A2:A8 and G2 are true (not text) dates, TEXT transformation will succeed. In G2 one would expect a month/year specification using a first day date, like 1-Feb-14.

What error did you get? By the way, it's important to have the right formatting string: mmmyy stands for month and year. In French for example, this would be different.

And if G2 is text (not a number)...

=SUMPRODUCT(     
  --(TEXT($A$2:$A$8,"mmmyy")=G2),     
  --($B$2:$B$8=G3),     
  $C$2:$C$8,     
  $D$2:$D$8)

will succeed as intended.

 

[markmzz]

Hi Vijayamani1987,

I forgot a important information (in red).

DATE	NAME	MRP	QTY
11/02/2014	CLIP	5	5		DATE	fev 14
18/02/2014	CLIP	5	7		NAME	CLIP	For the formula below use Enter to enter the formula
28/02/2014	RING	16	4		TOTAL	108	=SUM(INDEX((B2:B8=G3)*(A2:A8-DAY(A2:A8)=G2-DAY(G2))*C2:C8*D2:D8,))
03/04/2014	RING	14	2		TOTAL	108	=SUM((B2:B8=G3)*(A2:A8-DAY(A2:A8)=G2-DAY(G2))*C2:C8*D2:D8)
14/04/2014	RING	20	2				For the formula above use Ctrl+Shift+Enter to enter the formula
22/02/2014	CLIP	8	6
06/04/2014	CLIP	7	4
**********	******	******	****	**	*******	********	*******************************************************************

<colgroup><col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" span="2"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="353" style="width: 265pt; mso-width-source: userset; mso-width-alt: 12909;"> <tbody> </tbody>

Markmzz

 

[vijayamani1987]

I tried all ur formula still i end with #value error. how can I upload my excel sheet here? or can anybody give me ur mail id? i will mail u my excel sheet?

 

[Aladin Akyurek]

I tried all ur formula still i end with #value error. how can I upload my excel sheet here? or can anybody give me ur mail id? i will mail u my excel sheet?

https://dl.dropboxusercontent.com/u/65698317/vijayamani1987.xlsx

 

[vijayamani1987]

hi thefile available here,

https://dl.dropboxusercontent.com/s/4c2zy730nqb1chf/mrexcel1.xlsm

 

[markmzz]

hi thefile available here,

https://dl.dropboxusercontent.com/s/4c2zy730nqb1chf/mrexcel1.xlsm

Try this:

In D8 - use Ctrl+Shift+Enter to enter the formula

=SUM((Master!$E$12:$E$100=C8)*(Master!$B$12:$B$100-DAY(Master!$B$12:$B$100)=$E$5-DAY($E$5))*
IF(ISNUMBER(Master!$F$12:$F$100*Master!$G$12:$G$100),Master!$F$12:$F$100*Master!$G$12:$G$100))

Markmzz

 

[Aladin Akyurek]

hi thefile available here,

https://dl.dropboxusercontent.com/s/4c2zy730nqb1chf/mrexcel1.xlsm

D8:

=SUMPRODUCT(
  --(Master!$E$12:$E$100=$C8),
  --(TEXT(Master!$B$12:$B$100,"mmmyy")=TEXT(E$5,"mmmyy")),
  Master!$F$12:$F$100,
  Master!$G$12:$G$100)

Also:

=SUMPRODUCT(
  --(Master!$E$12:$E$100=$C8),
  --(Master!$B$12:$B$100-DAY(Master!$B$12:$B$100)+1=E$5),
  Master!$F$12:$F$100,
  Master!$G$12:$G$100)

 

[vijayamani1987]

,