multiple IF function formula to match and get result

[RAJESH1960]

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
	A	B	C	D	E	F
1	Line	AS PER	ID	RESULT	NUMBER	AMOUNT
2	1	PORTAL	29ABCD	A1234	A1234	1000
3	7	BOOKS	29ABCD	A1234		1000
4	13	PORTAL	29GHIJ	A1240	A1240	1000
5	2	PORTAL	29CDEF	A1235	A1235	2000
6	8	BOOKS	29CDEF	A1235		2000
7	3	PORTAL	29DEFG	A1236	A1236	3000
8	9	BOOKS	29DEFG	A1236		3000
9	14	BOOKS	29DEFG	Not in Portal		3500
10	4	PORTAL	33ABCD	A1237	A1237	4000
11	10	BOOKS	33ABCD	A1237		4000
12	5	PORTAL	33CDEF	A1238	A1238	5000
13	11	BOOKS	33CDEF	A1238		5000
14	6	PORTAL	33DEFG	A1239	A1239	6000
15	12	BOOKS	33DEFG	A1239		6000
Sheet1

 

[jasonb75]

Can't remember what I suggested previously, but think I may have answered the previous one, the sample looks familiar.

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Line	AS PER	ID	RESULT	NUMBER	AMOUNT
2	1	PORTAL	29ABCD	A1234	A1234	1000
3	7	BOOKS	29ABCD	A1234		1000
4	13	PORTAL	29GHIJ	A1240	A1240	1000
5	2	PORTAL	29CDEF	A1235	A1235	2000
6	8	BOOKS	29CDEF	A1235		2000
7	3	PORTAL	29DEFG	A1236	A1236	3000
8	9	BOOKS	29DEFG	A1236		3000
9	14	BOOKS	29DEFG	Not In Portal		3500
10	4	PORTAL	33ABCD	A1237	A1237	4000
11	10	BOOKS	33ABCD	A1237		4000
12	5	PORTAL	33CDEF	A1238	A1238	5000
13	11	BOOKS	33CDEF	A1238		5000
14	6	PORTAL	33DEFG	A1239	A1239	6000
15	12	BOOKS	33DEFG	A1239		6000
Sheet10

Cell Formulas
Range	Formula
D2:D15	D2	=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")

 

[RAJESH1960]

Maybe

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Line	AS PER	ID	RESULT	NUMBER	AMOUNT
2	1	PORTAL	29ABCD	A1234	A1234	1000
3	7	BOOKS	29ABCD	A1234		1000
4	13	PORTAL	29GHIJ	A1240	A1240	1000
5	2	PORTAL	29CDEF	A1235	A1235	2000
6	8	BOOKS	29CDEF	A1235		2000
7	3	PORTAL	29DEFG	A1236	A1236	3000
8	9	BOOKS	29DEFG	A1236		3000
9	14	BOOKS	29DEFG	Not in portal		3500
10	4	PORTAL	33ABCD	A1237	A1237	4000
11	10	BOOKS	33ABCD	A1237		4000
12	5	PORTAL	33CDEF	A1238	A1238	5000
13	11	BOOKS	33CDEF	A1238		5000
14	6	PORTAL	33DEFG	A1239	A1239	6000
15	12	BOOKS	33DEFG	A1239		6000
Master

Cell Formulas
Range	Formula
D2:D15	D2	=IF(B2="Portal",E2,IF(COUNTIFS(B:B,"Portal",C:C,C2,F:F,F2),D1,"Not in portal"))

Perfect. Thanks Fluff.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[RAJESH1960]

Can't remember what I suggested previously, but think I may have answered the previous one, the sample looks familiar.

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Line	AS PER	ID	RESULT	NUMBER	AMOUNT
2	1	PORTAL	29ABCD	A1234	A1234	1000
3	7	BOOKS	29ABCD	A1234		1000
4	13	PORTAL	29GHIJ	A1240	A1240	1000
5	2	PORTAL	29CDEF	A1235	A1235	2000
6	8	BOOKS	29CDEF	A1235		2000
7	3	PORTAL	29DEFG	A1236	A1236	3000
8	9	BOOKS	29DEFG	A1236		3000
9	14	BOOKS	29DEFG	Not In Portal		3500
10	4	PORTAL	33ABCD	A1237	A1237	4000
11	10	BOOKS	33ABCD	A1237		4000
12	5	PORTAL	33CDEF	A1238	A1238	5000
13	11	BOOKS	33CDEF	A1238		5000
14	6	PORTAL	33DEFG	A1239	A1239	6000
15	12	BOOKS	33DEFG	A1239		6000
Sheet10

Cell Formulas
Range	Formula
D2:D15	D2	=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.