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.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWX
1DATEPARTICULARSNUMBERHelperDEBITCREDIT1Amt2Amt3Amt4Amt5Amt6Amt7Amt8Amt
201-02-2021JANUARY10011-1001001JANUARY-100FEBRUARY4300MARCH-300APRIL-400MAY-500JUNE-600JULY-700AUGUST-800
301-02-2021FEBRUARY1001243001002JANUARY-500FEBRUARY500 0 0 0 0 0 0
401-02-2021MARCH10013-3001003JANUARY-100MARCH-200APRIL-300BANK650CC-50 0 0 0
501-02-2021APRIL10014-4001004 0 0 0 0 0 0 0 0
601-02-2021MAY10015-500
701-02-2021JUNE10016-600
801-02-2021JULY10017-700
901-02-2021AUGUST10018-800 
1001-02-2021SEPTEMBER10019-900
1101-02-2021JANUARY10021-500
1201-02-2021FEBRUARY10022500
1302-02-2021JANUARY10031-100
1402-02-2021MARCH10032-200
1502-02-2021APRIL10033-300
1602-02-2021BANK10034650
1702-02-2021CC10035-50
18Total-54505450
Solved
Cell Formulas
RangeFormula
W9,I2:I5,W2:W5,U2:U5,S2:S5,Q2:Q5,O2:O5,M2:M5,K2:K5I2=IFERROR(VLOOKUP($H2&"/"&I$1,CHOOSE({1,2},$C$2:$C$17&"/"&$D$2:$D$17,$B$2:$B$17),2,0),"")
J2:J5J2=SUMPRODUCT(--($B$2:$B$17=I2)*($C$2:$C$17=H2)*($E$2:$F$17))
L2:L5L2=SUMPRODUCT(--($B$2:$B$17=K2)*($C$2:$C$17=H2)*($E$2:$F$17))
N2:N5N2=SUMPRODUCT(--($B$2:$B$17=M2)*($C$2:$C$17=H2)*($E$2:$F$17))
P2:P5P2=SUMPRODUCT(--($B$2:$B$17=O2)*($C$2:$C$17=H2)*($E$2:$F$17))
R2:R5R2=SUMPRODUCT(--($B$2:$B$17=Q2)*($C$2:$C$17=H2)*($E$2:$F$17))
T2:T5T2=SUMPRODUCT(--($B$2:$B$17=S2)*($C$2:$C$17=H2)*($E$2:$F$17))
V2:V5V2=SUMPRODUCT(--($B$2:$B$17=U2)*($C$2:$C$17=H2)*($E$2:$F$17))
X2:X5X2=SUMPRODUCT(--($B$2:$B$17=W2)*($C$2:$C$17=H2)*($E$2:$F$17))
D2:D17D2=IF(C1=C2,D1+1,1)
E18:F18E18=SUM(E2:E17)
Press CTRL+SHIFT+ENTER to enter array formulas.

Perfect...😀
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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