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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,943
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 26, 2020
Messages
643
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,508
Office Version
  1. 365
Platform
  1. Windows
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

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

ADVERTISEMENT

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

Well-known Member
Joined
Mar 26, 2020
Messages
643
Office Version
  1. 2019
Platform
  1. Windows
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

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

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,943
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 26, 2020
Messages
643
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,217
Office Version
  1. 365
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"))
 
Solution

Forum statistics

Threads
1,140,996
Messages
5,703,623
Members
421,306
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