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...

 

[vijayamani1987]

Thank you everyone of you...
Now I am here with a new problem

	A	B	C	D	E	F	G
1	Date	Name	Qty
2	1/03/13	Clip	5		Date	01/03/13
3	16/03/13	Pen	9		Name	Clip
4	12/01/13	Pen	4		Total Qty	12
5	24/02/13	Pen	2
6	1/01/13	Clip	3
7	11/12/12	Clip	2
8	28/3/13	Clip	7
9	19/12/12	Pen	6

<tbody>
</tbody>

As shown in the above table, in Cell "F4", I need a formula which will sum column "C" based on the required values from column "A & B", Cell "F2" and column "A" should have the same month & year ( no need for same date in "F2" & column "A").

I have tried this formula. But it gives =0.

F4=sumifs(c2:c9,A2:A9,"="&F2,B2:B9,"="&F3)

Please correct my formula or suggest a good one.

 

[Aladin Akyurek]

Thank you everyone of you...
Now I am here with a new problem

	A	B	C	D	E	F	G
1	Date	Name	Qty
2	1/03/13	Clip	5		Date	01/03/13
3	16/03/13	Pen	9		Name	Clip
4	12/01/13	Pen	4		Total Qty	12
5	24/02/13	Pen	2
6	1/01/13	Clip	3
7	11/12/12	Clip	2
8	28/3/13	Clip	7
9	19/12/12	Pen	6

<tbody>
</tbody>

As shown in the above table, in Cell "F4", I need a formula which will sum column "C" based on the required values from column "A & B", Cell "F2" and column "A" should have the same month & year ( no need for same date in "F2" & column "A").

I have tried this formula. But it gives =0.

F4=sumifs(c2:c9,A2:A9,"="&F2,B2:B9,"="&F3)

Please correct my formula or suggest a good one.

Try...

=SUMIFS(C2:C9,B2:B9,F3,A2:A9,">="&F2,A2:A9,"<="&EOMONTH(F2,0))

 

[vijayamani1987]

Really nice...that works pretty well.
Thank you Aladin Akyurek

 

[markmzz]

So, do you normally just manually translate your suggested formulas to their English equivalents?

No. I have a macro to do that, but some times I have to do a small modification in the macro result.

Markmzz

 

[markmzz]

Thank you everyone of you...
Now I am here with a new problem

	A	B	C	D	E	F	G
1	Date	Name	Qty
2	1/03/13	Clip	5		Date	01/03/13
3	16/03/13	Pen	9		Name	Clip
4	12/01/13	Pen	4		Total Qty	12
5	24/02/13	Pen	2
6	1/01/13	Clip	3
7	11/12/12	Clip	2
8	28/3/13	Clip	7
9	19/12/12	Pen	6

<tbody>
</tbody>

As shown in the above table, in Cell "F4", I need a formula which will sum column "C" based on the required values from column "A & B", Cell "F2" and column "A" should have the same month & year ( no need for same date in "F2" & column "A").

I have tried this formula. But it gives =0.

F4=sumifs(c2:c9,A2:A9,"="&F2,B2:B9,"="&F3)

Please correct my formula or suggest a good one.

You can try this too:

=SUMPRODUCT(-(B2:B9=F3),-(TEXT(A2:A9,"mmyy")=TEXT(F2,"mmyy")),C2:C9)

Markmzz

 

[Aladin Akyurek]

Really nice...that works pretty well.
Thank you Aladin Akyurek

You're welcome.

 

[vijayamani1987]

Hi friends,
Here is my next problem.

	A	B	C	D	E	F	G	H
1	DATE	NAME	MRP	QTY
2	11/02/14	CLIP	5	5		DATE	FEB 14
3	18/02/14	CLIP	5	7		NAME	CLIP
4	29/02/14	RING	16	4		TOTAL	108
5	03/04/14	RING	14	2
6	14/04/14	RING	20	2
7	22/02/14	CLIP	8	6
8	06/04/14	CLIP	7	4
9
10

<tbody>
</tbody>

here, the MRP of the product wont be same for the same day. It will vary to each customer. So in total (G4), I want the sum by multiplying each quantity in the suitable row with the corresponding MRP and sum it for the required month.

I can do it by multiplying C & D column as default and can use those values in sumifs formula.

Is there any other way to find the G4 value? Thanks in advance for the help.

 

[Aladin Akyurek]

Hi friends,
Here is my next problem.

	A	B	C	D	E	F	G	H
1	DATE	NAME	MRP	QTY
2	11/02/14	CLIP	5	5		DATE	FEB 14
3	18/02/14	CLIP	5	7		NAME	CLIP
4	29/02/14	RING	16	4		TOTAL	108
5	03/04/14	RING	14	2
6	14/04/14	RING	20	2
7	22/02/14	CLIP	8	6
8	06/04/14	CLIP	7	4
9
10

<tbody>
</tbody>

here, the MRP of the product wont be same for the same day. It will vary to each customer. So in total (G4), I want the sum by multiplying each quantity in the suitable row with the corresponding MRP and sum it for the required month.

I can do it by multiplying C & D column as default and can use those values in sumifs formula.

Is there any other way to find the G4 value? Thanks in advance for the help.

Try...

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

 

[markmzz]

Hi friends,
Here is my next problem.

	A	B	C	D	E	F	G	H
1	DATE	NAME	MRP	QTY
2	11/02/14	CLIP	5	5		DATE	FEB 14
3	18/02/14	CLIP	5	7		NAME	CLIP
4	29/02/14	RING	16	4		TOTAL	108
5	03/04/14	RING	14	2
6	14/04/14	RING	20	2
7	22/02/14	CLIP	8	6
8	06/04/14	CLIP	7	4
9
10

<tbody>
</tbody>

here, the MRP of the product wont be same for the same day. It will vary to each customer. So in total (G4), I want the sum by multiplying each quantity in the suitable row with the corresponding MRP and sum it for the required month.

I can do it by multiplying C & D column as default and can use those values in sumifs formula.

Is there any other way to find the G4 value? Thanks in advance for the help.

Try this too:

Use Ctrl+Shift+Enter to enter the formula

=SUM((B2:B8=G3)*(A2:A8-DAY(A2:A8)=G2-DAY(G2))*C2:C8*D2:D8)

Or

Use Enter to enter the formula

=SUM(INDEX((B2:B8=G3)*(A2:A8-DAY(A2:A8)=G2-DAY(G2))*C2:C8*D2:D8,))

Markmzz

 

[vijayamani1987]

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)