• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple IF Functions for one cell content

A

ajayxlnc2

New Member
Hi,

I need a multiple IF condition to be applied on the cell. The cell might contain SUN-XX, SUN-AU, SUN-SG, SUN-NZ, SUN-IN, SCO-AU, SCO-NZ, SCO-SG and many more like that. I want the IF condition to check if the cell contains the text SUN within the string of the cell then display SUN, if the cell contains the text SCO within the string of the cell then display SCO if both are not satisfied then display "none".

Please help me out
 
Hi ajayxlnc2,

Use below formula:
=IF(ISNUMBER(SEARCH("SUN",A1)),"SUN",IF(ISNUMBER(SEARCH("SCO",A1)),"SCO","None"))

Here your text is in cell A1.

Regards,
 
try this...

=CHOOSE(IFERROR(MATCH(LEFT($A1,3),{"Sun","Sco"},0),3),"Sun","Sco","None")

Assuming conditions are in A Column..
 
Thanks, both are working fine for me. But there is a problem, in the column in which i have to apply the IF conditions includes names also apart from SUN - AU, SUN - IN etc. For Example if the text in the cell is SUNNY then the formula should return "None" but this formula is returning "SUN". Is there any way that this could be fixed.
Also, i need to add one more condition for this.
There is another column in which there is text of Len() around 25-100 charecters but it is starting with "Infrastructure XXXX XXX XXXX XX....". I want to check "If the cell content in B1 is starting with "Infrastructure" then above mentioned formula should satisfy, else it should return "Manual".
 
Hi, ajayxlnc2!
Why don't you detail all your specs and requirements all together? This will help people making them work only once.
Regards!
 
Apart from this i also have another request for multiple IF Functions.
I have a status Column which has only 3 Status [Net, SCO, Manual].
I have 2 other columns Description (Column M) and Summary (Column N)
If the cell (W2) = [Exact value] "Net" then i want this formula to calculate =MID(M2,FIND("KS_Name=",M2),((FIND(";JobID",M2)-FIND("KS_Name=",M2)))) to display
And
If the cell (W2) = [Exact values]"SCO" then i want to pull the data from Summary Column (N2).
And
If the Cell (W2)= User then i want to just display "Manual"
Please help me out in this also.
 
Hi Ajay,

This is regarding your new request. Please consider uploading a sample file.
This is very helpful for both the Forum and OP (you).

I tried creating a sample data as per the details provided by you but I got stuck and could not complete.

Could you please explain below part in details.

=MID(M2,FIND("KS_Name=",M2),((FIND(";JobID",M2)-FIND("KS_Name=",M2))))
Click to expand...

What is KS_Name, JobID ?
Please provide details of Input, Output and the logic behind it.

P.S. - If I can suggest you one thing, if you get time please search "Nested If" on this portal.
There are various beautiful threads on the portal, these will help you to understand how to use Multiple If.
 
Last edited:
Thanks Throttleworks
Here is the explanation,
I have a Description Column, in each cell there is the description containing many charecters. Its an auto generated alert throught the system. From the description of each cell i want to pull the Alert Name which is also in the present in the cell in this format KS_Name=General_EventLogOmni4;JobID=223330;EventID=48624082
from this i want only the from between KS_ and ;JobID [Starting from KS_ till ; before the JobID].

KS_Name=General_EventLogOmni4;JobID=223330;EventID=48624082
FOr Example the above text in a cell which includes many others charecters also, from the description is extracted as follows
KS_Name=General_EventLogOmni4
I want this to be extracted when the Cell Contains = "Net"
 
Hi Ajay,

Sorry for late reply, was away, will reply with the formula shortly.

P.S. - Please check the file, sorry if I have understood it wrong.
 

Attachments

  • Ajay.xls
    33 KB · Views: 10
Last edited:
Hi Throttle Works,

I have uploaded the sample file.

Columns
Summary Description Alert Name Tool Effected

My Requirement is

One
First column i want to update is the Tool Column. The Condition is IF A1 starts with "Infrastructure" then the Tool might be Net or SCO else it is "Manual", but to determine whether it is Net or SCO then the formula should also check in the Column "Effected" it has to search the cell if it contains the text "NIQ" If it contains NIQ then display "NIQ", then it has to search in the cell for text "SCO", if it contains SCO then display "SCO", else display Manual..

Problem with searching for text SCO in the cell, there will be even names in the cells if there is Scott Styris in the cell then it contains "SCO", so the formula should only search for the text in the cell starting with "SCO" or "NIQ"

Two
Now i want to update the Alert Name column. If D1=NIQ, then it calculate the formula "=MID(A2,FIND("KS_Name=",A2),((FIND(";JobID",A2)-FIND("KS_Name=",A2))))" [I will explain this after this], if it D1=SCO then it should just pull the text from Description column and display that {Ex: Display B5}, if the cell contains Manual then it should display just "Manual".

Explanation for the above mentioned MID Formula.
Summary Column is an Auto Generated text, so has lot of charecters. But there is also a pattern in all the alerts for which the Tool is "NIQ" so if the Tool is NIQ then it should pull from the certain pattern from the cell in Summary Column. The example is shown in A1, A2, A3. The Pattern is i want KS_Name=xxxxxxx_xxxx;JobID=234516;EventID. From this i want only --> KS_Name=xxxxxxxx;<--JobID. I want only the text till ";" before the "JobID" i.e., ";JobID" starting from KS.

Please solve this problem

Sample file attached.
 

Attachments

  • Sample - Ajay.xlsx
    11 KB · Views: 13
Hi Somendra
If A1 doesn't start with "Infrastructure", then it should display "Manual" thats all. If it is starting with "Infrastructure" then it has to check whether it belongs to NIQ or SCO through the above said conditions
 
You must log in or register to reply here.
Back
Top