vlookup with multiple criteria

[RAJESH1960]

Hello
I need to give the vlookup formula in cell J2 - lookup value being H2 and H3, table array columns B,C, D, E and F and get the value from E. If E is blank I should get the value from F. and if both E and F are blank it should display blank.
I have been able to solve a part of the problem with the help of a helper column. But this function needs multiple if function too. I am not so good at it and unable to solve it.

Query to sort multiple data horizontally.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	DATE	PARTICULARS	NUMBER	Helper	DEBIT	CREDIT			1	Amt	2	Amt	3	Amt	4	Amt	5	Amt	6	Amt	7	Amt	8	Amt
2	01-02-2021	JANUARY	1001	1	-100			1001	JANUARY	#N/A	FEBRUARY		MARCH		APRIL		MAY		JUNE		JULY		AUGUST
3	01-02-2021	FEBRUARY	1001	2		4300		1002	JANUARY		FEBRUARY
4	01-02-2021	MARCH	1001	3	-300			1003	JANUARY		MARCH		APRIL		BANK		CC
5	01-02-2021	APRIL	1001	4	-400			1004
6	01-02-2021	MAY	1001	5	-500			1005
7	01-02-2021	JUNE	1001	6	-600			1006
8	01-02-2021	JULY	1001	7	-700			1007
9	01-02-2021	AUGUST	1001	8	-800			1008
10	01-02-2021	SEPTEMBER	1001	9	-900			1009
11	01-02-2021	JANUARY	1002	1	-500			1010
12	01-02-2021	FEBRUARY	1002	2		500		1011
13	02-02-2021	JANUARY	1003	1	-100			1012
14	02-02-2021	MARCH	1003	2	-200			1013
15	02-02-2021	APRIL	1003	3	-300			1014
16	02-02-2021	BANK	1003	4		650		1015
17	02-02-2021	CC	1003	5	-50			1016
18		Total			-5450	5450
OriginalData (2)

Cell Formulas
Range	Formula
J2	J2	=IFERROR(VLOOKUP(I$2&"/"&H$2,$B$2:$E$17,4,0),(VLOOKUP(I$2&"/"&H$2,$B$2:$F$17,5,0)))
D2:D17	D2	=IF(C1=C2,D1+1,1)
W2:W17,U2:U17,S2:S17,Q2:Q17,O2:O17,M2:M17,K2:K17,I2:I17	I2	=IFERROR(VLOOKUP($H2&"/"&I$1,CHOOSE({1,2},$C$2:$C$17&"/"&$D$2:$D$17,$B$2:$B$17),2,0),"")
E18:F18	E18	=SUM(E2:E17)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Michael M]

How about this in J2

=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))

Drag down as required

 

[RAJESH1960]

How about this in J2

=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))

Drag down as required

Micheal. The formula is correct in the cell J2. But in the remaining cells the 2nd is to be included in the formula. 2nd Condition if the matching column E is blank then it should take the value from the column F.

 

[Michael M]

My formula does take in to consideration that a cell may be blank

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	DATE	PARTICULARS	NUMBER	Helper	DEBIT	CREDIT			1	Amt	2	Amt
2	######	JANUARY	1001	1	-100			1001	JANUARY	-100	FEBRUARY	4300
3	######	February	1001	2		4300		1002	JANUARY	-500	FEBRUARY	500
4	######	MARCH	1001	3	-300			1003	JANUARY	-100	MARCH	-200
5	######	APRIL	1001	4	-400			1004	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
6	######	MAY	1001	5	-500			1005	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
7	######	JUNE	1001	6	-600			1006	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
8	######	JULY	1001	7	-700			1007	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
9	######	AUGUST	1001	8	-800			1008	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
10	######	SEPTEMBER	1001	9	-900			1009	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
11	######	JANUARY	1002	1	-500			1010	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
12	######	FEBRUARY	1002	2		500		1011	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
13	######	JANUARY	1003	1	-100			1012	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
14	######	MARCH	1003	2	-200			1013	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
15	######	APRIL	1003	3	-300			1014	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
16	######	BANK	1003	4		650		1015	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
17	######	CC	1003	5	-50			1016	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0	https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/	0
Sheet1

Cell Formulas
Range	Formula
J2:J17	J2	=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))
L2:L17	L2	=SUMPRODUCT(--($B$2:$B$17=K2)*($C$2:$C$17=H2)*($E$2:$F$17))

 

[Michael M]

My formula does take in to consideration that a cell may be blank
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td]

DATE​

[/td][td]

PARTICULARS​

[/td][td]

NUMBER​

[/td][td]

Helper​

[/td][td]

DEBIT​

[/td][td]

CREDIT​

[/td][td]

​

[/td][td]

​

[/td][td]

1​

[/td][td]Amt[/td][td]

2​

[/td][td]Amt[/td][/tr]

[tr][td]

2​

[/td][td]

######​

[/td][td]JANUARY[/td][td]

1001​

[/td][td]

1​

[/td][td]

-100​

[/td][td]

​

[/td][td]

​

[/td][td]

1001​

[/td][td]JANUARY[/td][td]

=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))​

[/td][td]FEBRUARY[/td][td]

=SUMPRODUCT(--($B$2:$B$17=K2)*($C$2:$C$17=H2)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

3​

[/td][td]

######​

[/td][td]February[/td][td]

1001​

[/td][td]

2​

[/td][td]

​

[/td][td]

4300​

[/td][td]

​

[/td][td]

1002​

[/td][td]JANUARY[/td][td]

=SUMPRODUCT(--($B$2:$B$17=I3)*($C$2:$C$17=H3)*($E$2:$F$17))​

[/td][td]FEBRUARY[/td][td]

=SUMPRODUCT(--($B$2:$B$17=K3)*($C$2:$C$17=H3)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

4​

[/td][td]

######​

[/td][td]MARCH[/td][td]

1001​

[/td][td]

3​

[/td][td]

-300​

[/td][td]

​

[/td][td]

​

[/td][td]

1003​

[/td][td]JANUARY[/td][td]

=SUMPRODUCT(--($B$2:$B$17=I4)*($C$2:$C$17=H4)*($E$2:$F$17))​

[/td][td]MARCH[/td][td]

=SUMPRODUCT(--($B$2:$B$17=K4)*($C$2:$C$17=H4)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

5​

[/td][td]

######​

[/td][td]APRIL[/td][td]

1001​

[/td][td]

4​

[/td][td]

-400​

[/td][td]

​

[/td][td]

​

[/td][td]

1004​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I5)*($C$2:$C$17=H5)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K5)*($C$2:$C$17=H5)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

6​

[/td][td]

######​

[/td][td]MAY[/td][td]

1001​

[/td][td]

5​

[/td][td]

-500​

[/td][td]

​

[/td][td]

​

[/td][td]

1005​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I6)*($C$2:$C$17=H6)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K6)*($C$2:$C$17=H6)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

7​

[/td][td]

######​

[/td][td]JUNE[/td][td]

1001​

[/td][td]

6​

[/td][td]

-600​

[/td][td]

​

[/td][td]

​

[/td][td]

1006​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I7)*($C$2:$C$17=H7)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K7)*($C$2:$C$17=H7)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

8​

[/td][td]

######​

[/td][td]JULY[/td][td]

1001​

[/td][td]

7​

[/td][td]

-700​

[/td][td]

​

[/td][td]

​

[/td][td]

1007​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I8)*($C$2:$C$17=H8)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K8)*($C$2:$C$17=H8)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

9​

[/td][td]

######​

[/td][td]AUGUST[/td][td]

1001​

[/td][td]

8​

[/td][td]

-800​

[/td][td]

​

[/td][td]

​

[/td][td]

1008​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I9)*($C$2:$C$17=H9)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K9)*($C$2:$C$17=H9)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

10​

[/td][td]

######​

[/td][td]SEPTEMBER[/td][td]

1001​

[/td][td]

9​

[/td][td]

-900​

[/td][td]

​

[/td][td]

​

[/td][td]

1009​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I10)*($C$2:$C$17=H10)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K10)*($C$2:$C$17=H10)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

11​

[/td][td]

######​

[/td][td]JANUARY[/td][td]

1002​

[/td][td]

1​

[/td][td]

-500​

[/td][td]

​

[/td][td]

​

[/td][td]

1010​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I11)*($C$2:$C$17=H11)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K11)*($C$2:$C$17=H11)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

12​

[/td][td]

######​

[/td][td]FEBRUARY[/td][td]

1002​

[/td][td]

2​

[/td][td]

​

[/td][td]

500​

[/td][td]

​

[/td][td]

1011​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I12)*($C$2:$C$17=H12)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K12)*($C$2:$C$17=H12)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

13​

[/td][td]

######​

[/td][td]JANUARY[/td][td]

1003​

[/td][td]

1​

[/td][td]

-100​

[/td][td]

​

[/td][td]

​

[/td][td]

1012​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I13)*($C$2:$C$17=H13)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K13)*($C$2:$C$17=H13)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

14​

[/td][td]

######​

[/td][td]MARCH[/td][td]

1003​

[/td][td]

2​

[/td][td]

-200​

[/td][td]

​

[/td][td]

​

[/td][td]

1013​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I14)*($C$2:$C$17=H14)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K14)*($C$2:$C$17=H14)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

15​

[/td][td]

######​

[/td][td]APRIL[/td][td]

1003​

[/td][td]

3​

[/td][td]

-300​

[/td][td]

​

[/td][td]

​

[/td][td]

1014​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I15)*($C$2:$C$17=H15)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K15)*($C$2:$C$17=H15)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

16​

[/td][td]

######​

[/td][td]BANK[/td][td]

1003​

[/td][td]

4​

[/td][td]

​

[/td][td]

650​

[/td][td]

​

[/td][td]

1015​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I16)*($C$2:$C$17=H16)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K16)*($C$2:$C$17=H16)*($E$2:$F$17))​

[/td][/tr]

[tr][td]

17​

[/td][td]

######​

[/td][td]CC[/td][td]

1003​

[/td][td]

5​

[/td][td]

-50​

[/td][td]

​

[/td][td]

​

[/td][td]

1016​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=I17)*($C$2:$C$17=H17)*($E$2:$F$17))​

[/td][td][/td][td]

=SUMPRODUCT(--($B$2:$B$17=K17)*($C$2:$C$17=H17)*($E$2:$F$17))​

[/td][/tr]
[/table]

 

[Michael M]

My formula does take in to consideration that a cell may be blank
Is this not the result you want ??
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

2​

[/td][td]

######​

[/td][td]JANUARY[/td][td]

1001​

[/td][td]

1​

[/td][td]

-100​

[/td][td]

​

[/td][td]

​

[/td][td]

1001​

[/td][td]JANUARY[/td][td]

-100​

[/td][td]FEBRUARY[/td][td]

4300​

[/td][/tr]

[tr][td]

3​

[/td][td]

######​

[/td][td]February[/td][td]

1001​

[/td][td]

2​

[/td][td]

​

[/td][td]

4300​

[/td][td]

​

[/td][td]

1002​

[/td][td]JANUARY[/td][td]

-500​

[/td][td]FEBRUARY[/td][td]

500​

[/td][/tr]

[tr][td]

4​

[/td][td]

######​

[/td][td]MARCH[/td][td]

1001​

[/td][td]

3​

[/td][td]

-300​

[/td][td]

​

[/td][td]

​

[/td][td]

1003​

[/td][td]JANUARY[/td][td]

-100​

[/td][td]MARCH[/td][td]

-200​

[/td][/tr]
[/table]

 

[RAJESH1960]

My formula does take in to consideration that a cell may be blank
Is this not the result you want ??

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

2​

######​

JANUARY

1001​

1​

-100​

​

​

1001​

JANUARY

-100​

FEBRUARY

4300​

3​

######​

February

1001​

2​

​

4300​

​

1002​

JANUARY

-500​

FEBRUARY

500​

4​

######​

MARCH

1001​

3​

-300​

​

​

1003​

JANUARY

-100​

MARCH

-200​

Yes, Exactly. Can you give me some idea to how do I download your sheet

 

[RAJESH1960]

Yes, Exactly. Can you give me some idea to how do I download your sheet

I am not even able to scroll the sheet

 

[Michael M]

I only posted a small sample to show you that I believe it worked
Can you copy the data in Post #5

 

[RAJESH1960]

I only posted a small sample to show you that I believe it worked
Can you copy the data in Post #5

Thank you Micheal. It took 2 days to find a solution to this problem. It works perfect. I can continue with the balance columns. Thanks once again.