vlookup with multiple criteria

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWX
1DATEPARTICULARSNUMBERHelperDEBITCREDIT1Amt2Amt3Amt4Amt5Amt6Amt7Amt8Amt
201-02-2021JANUARY10011-1001001JANUARY#N/AFEBRUARYMARCHAPRILMAYJUNEJULYAUGUST
301-02-2021FEBRUARY1001243001002JANUARYFEBRUARY      
401-02-2021MARCH10013-3001003JANUARYMARCHAPRILBANKCC   
501-02-2021APRIL10014-4001004        
601-02-2021MAY10015-5001005        
701-02-2021JUNE10016-6001006        
801-02-2021JULY10017-7001007        
901-02-2021AUGUST10018-8001008        
1001-02-2021SEPTEMBER10019-9001009        
1101-02-2021JANUARY10021-5001010        
1201-02-2021FEBRUARY100225001011        
1302-02-2021JANUARY10031-1001012        
1402-02-2021MARCH10032-2001013        
1502-02-2021APRIL10033-3001014        
1602-02-2021BANK100346501015        
1702-02-2021CC10035-501016        
18Total-54505450
OriginalData (2)
Cell Formulas
RangeFormula
J2J2=IFERROR(VLOOKUP(I$2&"/"&H$2,$B$2:$E$17,4,0),(VLOOKUP(I$2&"/"&H$2,$B$2:$F$17,5,0)))
D2:D17D2=IF(C1=C2,D1+1,1)
W2:W17,U2:U17,S2:S17,Q2:Q17,O2:O17,M2:M17,K2:K17,I2:I17I2=IFERROR(VLOOKUP($H2&"/"&I$1,CHOOSE({1,2},$C$2:$C$17&"/"&$D$2:$D$17,$B$2:$B$17),2,0),"")
E18:F18E18=SUM(E2:E17)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
How about this in J2
Excel Formula:
=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))
Drag down as required
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows
How about this in J2
Excel Formula:
=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

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
My formula does take in to consideration that a cell may be blank
Book1
ABCDEFGHIJKL
1DATEPARTICULARSNUMBERHelperDEBITCREDIT1Amt2Amt
2######JANUARY10011-1001001JANUARY-100FEBRUARY4300
3######February1001243001002JANUARY-500FEBRUARY500
4######MARCH10013-3001003JANUARY-100MARCH-200
5######APRIL10014-4001004https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
6######MAY10015-5001005https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
7######JUNE10016-6001006https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
8######JULY10017-7001007https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
9######AUGUST10018-8001008https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
10######SEPTEMBER10019-9001009https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
11######JANUARY10021-5001010https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
12######FEBRUARY100225001011https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
13######JANUARY10031-1001012https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
14######MARCH10032-2001013https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
15######APRIL10033-3001014https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
16######BANK100346501015https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
17######CC10035-501016https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0https://www.mrexcel.com/board/threads/vlookup-with-multiple-criteria.1163542/0
Sheet1
Cell Formulas
RangeFormula
J2:J17J2=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))
L2:L17L2=SUMPRODUCT(--($B$2:$B$17=K2)*($C$2:$C$17=H2)*($E$2:$F$17))
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

My formula does take in to consideration that a cell may be blank
Row\Col
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
=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))
FEBRUARY
=SUMPRODUCT(--($B$2:$B$17=K2)*($C$2:$C$17=H2)*($E$2:$F$17))
3​
######
February
1001
2
4300
1002
JANUARY
=SUMPRODUCT(--($B$2:$B$17=I3)*($C$2:$C$17=H3)*($E$2:$F$17))
FEBRUARY
=SUMPRODUCT(--($B$2:$B$17=K3)*($C$2:$C$17=H3)*($E$2:$F$17))
4​
######
MARCH
1001
3
-300
1003
JANUARY
=SUMPRODUCT(--($B$2:$B$17=I4)*($C$2:$C$17=H4)*($E$2:$F$17))
MARCH
=SUMPRODUCT(--($B$2:$B$17=K4)*($C$2:$C$17=H4)*($E$2:$F$17))
5​
######
APRIL
1001
4
-400
1004
=SUMPRODUCT(--($B$2:$B$17=I5)*($C$2:$C$17=H5)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K5)*($C$2:$C$17=H5)*($E$2:$F$17))
6​
######
MAY
1001
5
-500
1005
=SUMPRODUCT(--($B$2:$B$17=I6)*($C$2:$C$17=H6)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K6)*($C$2:$C$17=H6)*($E$2:$F$17))
7​
######
JUNE
1001
6
-600
1006
=SUMPRODUCT(--($B$2:$B$17=I7)*($C$2:$C$17=H7)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K7)*($C$2:$C$17=H7)*($E$2:$F$17))
8​
######
JULY
1001
7
-700
1007
=SUMPRODUCT(--($B$2:$B$17=I8)*($C$2:$C$17=H8)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K8)*($C$2:$C$17=H8)*($E$2:$F$17))
9​
######
AUGUST
1001
8
-800
1008
=SUMPRODUCT(--($B$2:$B$17=I9)*($C$2:$C$17=H9)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K9)*($C$2:$C$17=H9)*($E$2:$F$17))
10​
######
SEPTEMBER
1001
9
-900
1009
=SUMPRODUCT(--($B$2:$B$17=I10)*($C$2:$C$17=H10)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K10)*($C$2:$C$17=H10)*($E$2:$F$17))
11​
######
JANUARY
1002
1
-500
1010
=SUMPRODUCT(--($B$2:$B$17=I11)*($C$2:$C$17=H11)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K11)*($C$2:$C$17=H11)*($E$2:$F$17))
12​
######
FEBRUARY
1002
2
500
1011
=SUMPRODUCT(--($B$2:$B$17=I12)*($C$2:$C$17=H12)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K12)*($C$2:$C$17=H12)*($E$2:$F$17))
13​
######
JANUARY
1003
1
-100
1012
=SUMPRODUCT(--($B$2:$B$17=I13)*($C$2:$C$17=H13)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K13)*($C$2:$C$17=H13)*($E$2:$F$17))
14​
######
MARCH
1003
2
-200
1013
=SUMPRODUCT(--($B$2:$B$17=I14)*($C$2:$C$17=H14)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K14)*($C$2:$C$17=H14)*($E$2:$F$17))
15​
######
APRIL
1003
3
-300
1014
=SUMPRODUCT(--($B$2:$B$17=I15)*($C$2:$C$17=H15)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K15)*($C$2:$C$17=H15)*($E$2:$F$17))
16​
######
BANK
1003
4
650
1015
=SUMPRODUCT(--($B$2:$B$17=I16)*($C$2:$C$17=H16)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K16)*($C$2:$C$17=H16)*($E$2:$F$17))
17​
######
CC
1003
5
-50
1016
=SUMPRODUCT(--($B$2:$B$17=I17)*($C$2:$C$17=H17)*($E$2:$F$17))
=SUMPRODUCT(--($B$2:$B$17=K17)*($C$2:$C$17=H17)*($E$2:$F$17))
 
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I only posted a small sample to show you that I believe it worked
Can you copy the data in Post #5
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,165
Members
417,128
Latest member
Xianter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top