multiple IF function formula to match and get result

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
643
Office Version
  1. 2019
Platform
  1. Windows
Hello Guys,

I have asked this question before already and I think I got a solution too from this board. But I am not able to search the solution in all the 450 and odd messages. Lately I have started saving the solutions in the bookmark folder. So, I am once again placing somewhat a similar query. As per the image, I have to get the NUMBER in coluumn D in t books column as per portal. If portal, ID and Amounts are same then I must get the NUMBER as per portal to the books row. If as per books, the amount is not blank & if Portal, ID and Amounts are not matching then it should display "Not in Portal". End result, I want the Result in all the cells in column D and none of the cells should be blank.

Query to get V No..xlsx
ABCDEF
1LineAS PERIDRESULTNUMBERAMOUNT
21PORTAL29ABCDA1234A12341000
37BOOKS29ABCDA12341000
413PORTAL29GHIJA1240A12401000
52PORTAL29CDEFA1235A12352000
68BOOKS29CDEFA12352000
73PORTAL29DEFGA1236A12363000
89BOOKS29DEFGA12363000
914BOOKS29DEFGNot in Portal3500
104PORTAL33ABCDA1237A12374000
1110BOOKS33ABCDA12374000
125PORTAL33CDEFA1238A12385000
1311BOOKS33CDEFA12385000
146PORTAL33DEFGA1239A12396000
1512BOOKS33DEFGA12396000
Sheet1
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,507
Office Version
  1. 365
Platform
  1. Windows
Can't remember what I suggested previously, but think I may have answered the previous one, the sample looks familiar.
Book1 (version 1).xlsb
ABCDEF
1LineAS PERIDRESULTNUMBERAMOUNT
21PORTAL29ABCDA1234A12341000
37BOOKS29ABCDA12341000
413PORTAL29GHIJA1240A12401000
52PORTAL29CDEFA1235A12352000
68BOOKS29CDEFA12352000
73PORTAL29DEFGA1236A12363000
89BOOKS29DEFGA12363000
914BOOKS29DEFGNot In Portal3500
104PORTAL33ABCDA1237A12374000
1110BOOKS33ABCDA12374000
125PORTAL33CDEFA1238A12385000
1311BOOKS33CDEFA12385000
146PORTAL33DEFGA1239A12396000
1512BOOKS33DEFGA12396000
Sheet10
Cell Formulas
RangeFormula
D2:D15D2=IFERROR(INDEX($E$2:$E$15,AGGREGATE(15,6,ROW($E$2:$E$15)/($C$2:$C$15=C2)/($F$2:$F$15=F2)/($B$2:$B$15="Portal"),1)-ROW($E$2)+1),"Not In Portal")
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
643
Office Version
  1. 2019
Platform
  1. Windows
Maybe
+Fluff 1.xlsm
ABCDEF
1LineAS PERIDRESULTNUMBERAMOUNT
21PORTAL29ABCDA1234A12341000
37BOOKS29ABCDA12341000
413PORTAL29GHIJA1240A12401000
52PORTAL29CDEFA1235A12352000
68BOOKS29CDEFA12352000
73PORTAL29DEFGA1236A12363000
89BOOKS29DEFGA12363000
914BOOKS29DEFGNot in portal3500
104PORTAL33ABCDA1237A12374000
1110BOOKS33ABCDA12374000
125PORTAL33CDEFA1238A12385000
1311BOOKS33CDEFA12385000
146PORTAL33DEFGA1239A12396000
1512BOOKS33DEFGA12396000
Master
Cell Formulas
RangeFormula
D2:D15D2=IF(B2="Portal",E2,IF(COUNTIFS(B:B,"Portal",C:C,C2,F:F,F2),D1,"Not in portal"))
Perfect. Thanks Fluff.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,215
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
643
Office Version
  1. 2019
Platform
  1. Windows
Can't remember what I suggested previously, but think I may have answered the previous one, the sample looks familiar.
Book1 (version 1).xlsb
ABCDEF
1LineAS PERIDRESULTNUMBERAMOUNT
21PORTAL29ABCDA1234A12341000
37BOOKS29ABCDA12341000
413PORTAL29GHIJA1240A12401000
52PORTAL29CDEFA1235A12352000
68BOOKS29CDEFA12352000
73PORTAL29DEFGA1236A12363000
89BOOKS29DEFGA12363000
914BOOKS29DEFGNot In Portal3500
104PORTAL33ABCDA1237A12374000
1110BOOKS33ABCDA12374000
125PORTAL33CDEFA1238A12385000
1311BOOKS33CDEFA12385000
146PORTAL33DEFGA1239A12396000
1512BOOKS33DEFGA12396000
Sheet10
Cell Formulas
RangeFormula
D2:D15D2=IFERROR(INDEX($E$2:$E$15,AGGREGATE(15,6,ROW($E$2:$E$15)/($C$2:$C$15=C2)/($F$2:$F$15=F2)/($B$2:$B$15="Portal"),1)-ROW($E$2)+1),"Not In Portal")
Thanks Jasonb. Your formula also works but I have to change the range for every sheet. So, I would like to go with Fluff's formula. Thanks for responding and your time.
 

Forum statistics

Threads
1,140,993
Messages
5,703,611
Members
421,305
Latest member
ambuj Thakur

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