# multiple IF function formula to match and get result

#### RAJESH1960

##### Well-known Member
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
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
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
Glad we could help & thanks for the feedback.

#### RAJESH1960

##### Well-known Member
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.

Replies
2
Views
103
Replies
28
Views
183
Replies
1
Views
63
Replies
1
Views
59
Replies
13
Views
299

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.

### Which adblocker are you using?

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

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