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

 

[Joe4]

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

You can search on this board (Search threads), and limit your search to threads started by you, and look for certain keywords, and you should be able to find it.

 

[RAJESH1960]

You can search on this board (Search threads), and limit your search to threads started by you, and look for certain keywords, and you should be able to find it.

First thing I did was exactly that. But I was not able to find it. I went to messages and checked most of the messages but I couldn't find it. So I have posted it once again. Is there any other easier way to find it.?

 

[jasonb75]

There is a link to 'Your Threads' at the top of the page that will show you only the first post of each thread that you have started. This means that you only have to look at 77, not 450.

You can find it by clicking the arrow next to 'Find Threads' just below the MrExcel logo in the top left corner of the page.

 

[RAJESH1960]

There is a link to 'Your Threads' at the top of the page that will show you only the first post of each thread that you have started. This means that you only have to look at 77, not 450.

You can find it by clicking the arrow next to 'Find Threads' just below the MrExcel logo in the top left corner of the page.

Got it Jasonb. Will now check for the solution. Thanks

 

[RAJESH1960]

Hey Guys, Checked and found out that this query is quite different from the one I had asked before. So the question stands. Can anyone guide and help me with the above problem.?
Please note.. The portal Number in all the cases remains the same even if it doesn't match with the books.

 

[RAJESH1960]

I think this post is closed. I will ask the same in a new post.

 

[Joe4]

I think this post is closed. I will ask the same in a new post.

No, the post is not closed. Please do not post a new question on this topic.

Personally, I do not fully understand your question, but I am a very visual person. If you could post a sample of what your data looks like BEFORE you do anything, and then another sample of what your expected output looks like AFTER, that may help clarify things for me.

 

[RAJESH1960]

No, the post is not closed. Please do not post a new question on this topic.

Personally, I do not fully understand your question, but I am a very visual person. If you could post a sample of what your data looks like BEFORE you do anything, and then another sample of what your expected output looks like AFTER, that may help clarify things for me.

Ok Joe4.I will post 2 different excel sheets for you to understand better. Give me sometime.

 

[Fluff]

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"))