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.

 

[RAJESH1960]

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.

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	-100	FEBRUARY	4300	MARCH	-300	APRIL	-400	MAY	-500	JUNE	-600	JULY	-700	AUGUST	-800
3	01-02-2021	FEBRUARY	1001	2		4300		1002	JANUARY	-500	FEBRUARY	500		0		0		0		0		0		0
4	01-02-2021	MARCH	1001	3	-300			1003	JANUARY	-100	MARCH	-200	APRIL	-300	BANK	650	CC	-50		0		0		0
5	01-02-2021	APRIL	1001	4	-400			1004		0		0		0		0		0		0		0		0
6	01-02-2021	MAY	1001	5	-500
7	01-02-2021	JUNE	1001	6	-600
8	01-02-2021	JULY	1001	7	-700
9	01-02-2021	AUGUST	1001	8	-800
10	01-02-2021	SEPTEMBER	1001	9	-900
11	01-02-2021	JANUARY	1002	1	-500
12	01-02-2021	FEBRUARY	1002	2		500
13	02-02-2021	JANUARY	1003	1	-100
14	02-02-2021	MARCH	1003	2	-200
15	02-02-2021	APRIL	1003	3	-300
16	02-02-2021	BANK	1003	4		650
17	02-02-2021	CC	1003	5	-50
18		Total			-5450	5450
Solved

Cell Formulas
Range	Formula
W9,I2:I5,W2:W5,U2:U5,S2:S5,Q2:Q5,O2:O5,M2:M5,K2:K5	I2	=IFERROR(VLOOKUP($H2&"/"&I$1,CHOOSE({1,2},$C$2:$C$17&"/"&$D$2:$D$17,$B$2:$B$17),2,0),"")
J2:J5	J2	=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))
L2:L5	L2	=SUMPRODUCT(--($B$2:$B$17=K2)*($C$2:$C$17=H2)*($E$2:$F$17))
N2:N5	N2	=SUMPRODUCT(--($B$2:$B$17=M2)*($C$2:$C$17=H2)*($E$2:$F$17))
P2:P5	P2	=SUMPRODUCT(--($B$2:$B$17=O2)*($C$2:$C$17=H2)*($E$2:$F$17))
R2:R5	R2	=SUMPRODUCT(--($B$2:$B$17=Q2)*($C$2:$C$17=H2)*($E$2:$F$17))
T2:T5	T2	=SUMPRODUCT(--($B$2:$B$17=S2)*($C$2:$C$17=H2)*($E$2:$F$17))
V2:V5	V2	=SUMPRODUCT(--($B$2:$B$17=U2)*($C$2:$C$17=H2)*($E$2:$F$17))
X2:X5	X2	=SUMPRODUCT(--($B$2:$B$17=W2)*($C$2:$C$17=H2)*($E$2:$F$17))
D2:D17	D2	=IF(C1=C2,D1+1,1)
E18:F18	E18	=SUM(E2:E17)
Press CTRL+SHIFT+ENTER to enter array formulas.

Perfect...?