Vlookup/sumproduct for two or more criteria

[Vanda_a]

Dear all

Could you guy help me out with the VLOOKUP. below are the table i would like to use the VLOOKUP.

Num	AC	Name		Num
1	11002	ABC		1	Apple	How to vlookup Num and meet the criteria 42 of column AC then extract value of Name
1	11001	BCA		2	Orange
1	42001	Apple		3	Mango
2	11002	ABC
2	11001	BCA
2	42002	Orange
3	11003	ABC
3	11005	BCA
3	42005	Mango

<tbody>
</tbody>

Case 2. Sumproduct with two and more criteria. Note : the formula working with only two criteria.
I dont know how to add more criteria and sum two column C and D

	A	B	C	D	E	F	G
1	Num	AC	Dr	Cr	Code		Num	42001
2	1	11002	100		T		1	=sumproduct((A2:A15=G2)*(B2:B15=H1)*(E2:E15="T"),C2:D15)
3	1	11001	100		N		2
4	1	42001		200	T		3
5	1	42001		20	N
6
7	2	11002	100		T
8	2	11001	100		N
9	2	42002		200	T
10	2	42002		20	N
11
12	3	11003	100		T
13	3	11005	100		N
14	3	42005		200	T
15	3	42005		20	N

<tbody>
</tbody>

Thank you very much

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	AC
1	Num	AC	Name		Num						criteria
2	1	11002	ABC		1	Apple	How to vlookup Num and meet the criteria 42 of column AC then extract value of Name				42
3	1	11001	BCA		2	Orange
4	1	42001	Apple		3	Mango
5
6	2	11002	ABC
7	2	11001	BCA
8	2	42002	Orange
9
10	3	11003	ABC
11	3	11005	BCA
12	3	42005	Mango

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula F2 {=INDEX($C$2:$C$12,MATCH(E2&"|"&$AC$2,$A$2:$A$12&"|"&LEFT($B$2:$B$12,LEN($AC$2)),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Case 2:

	A	B	C	D	E	F	G	H
1	Num	AC	Dr	Cr	Code		Num	42001
2	1	11002	100		T		1	200
3	1	11001	100		N		2	0
4	1	42001		200	T		3	0
5	1	42001		20	N
6
7	2	11002	100		T
8	2	11001	100		N
9	2	42002		200	T
10	2	42002		20	N
11
12	3	11003	100		T
13	3	11005	100		N
14	3	42005		200	T
15	3	42005		20	N

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula H2 =SUMPRODUCT(($A$2:$A$15=G2)*($B$2:$B$15=$H$1)*($E$2:$E$15="T"),($C$2:$C$15+$D$2:$D$15)) <tbody> </tbody>

<tbody>
</tbody>

 

[Vanda_a]

Try:

Good morning. For sumproduct i tried but it not working. why?

=SUMPRODUCT(('JL All'!F:F='P&L Detail for Tax'!D9)*('JL All'!Q:Q="VAT10")*('JL All'!Q:Q="WHT10")*('JL All'!Q:Q="WHT14"),('JL All'!I:I))

 

[Vanda_a]

By the way, may i have any other alternative formula to replace sumproduct. Because sumproduct is lacking my file really much. i got 10k rows with 10 columns full of sumproduct :T_T:

Thank you

 

[Vanda_a]

I know why it does not work. Due to F:F. if F1:F10, it works. But why?
And how to change H1 to 41 and still keep the formula work?

Thank you

 

[Eric W]

A SUMIFS would be more efficient than SUMPRODUCT in this case. Try:

Excel 2012
	A	B	C	D	E	F	G	H	I
1	Num	AC	Dr	Cr	Code		Num	42001	42999
2	1	11002	100		T		1	200
3	1	11001	100		N		2	200
4	1	42001		200	T		3	200
5	1	42001		20	N
6
7	2	11002	100		T
8	2	11001	100		N
9	2	42002		200	T
10	2	42002		20	N
11
12	3	11003	100		T
13	3	11005	100		N
14	3	42005		200	T
15	3	42005		20	N
Sheet2

Cell Formulas
Range	Formula
H2	=SUMIFS(C:C,A:A,G2,E:E,"T",B:B,">="&$H$1,B:B,"<="&$I$1)+SUMIFS(D:D,A:A,G2,E:E,"T",B:B,">="&$H$1,B:B,"<="&$I$1)

Note that I used a >= and a <= condition for column B. If the values in that column are numbers, then you need numeric conditions. If they are numbers saved as text, then you could use something like "42*".

Also note that I used whole column references in the formula. You should NOT use whole column references in array formulas such as SUMPRODUCT, but in some Excel functions, such as MAX, SUM, SUMIFS, etc. Excel is aware of the used range and uses it when calculating it. Be aware of what type of function you're using before using whole column references.

 

[Vanda_a]

Good morning. It works. Thank you.
May I check one more issue please?
This mark * not working. Or I should use this mark instead? !?*