understanding how to set a value in one column to equal something based on contents of another column

Matt Pinkston
Matt Pinkston used Ask the Experts™
on
Need help on understanding how to set a value in one column to equal something based on contents of another column

example 1:
I would like to set column B = to Growth if column A begins with Growth-
I would like to set column B = to GRC if column A begins with Compliance-
I would like to set column B = to Analytics if column A contains Portal-Salesforce Connector
I would like to set column B = to Analytics if column A contains Portal-Microsoft Connector
I would like to set column B = to Portal if column A begins with Portal-

example 2:
I would like to set column C = to SalesForce if column A Contains SalesForce
I would like to set column C = to Dynamics if column A Contains Microsoft
I would like to set column C = to Portal if column A contains Portal and not either of above
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
There are a couple of options:

Nested IF statements
You can use an IF statement to determine the value of a cell and return a desired value, syntax is:

=IF(Condition, True Result, False Result)

or in English If condition is true give true result otherwise give false result.

The false result can be another IF statement, ie

=IF(Condition1, True Result1, IF(Condition2, True Result2,IF(Condition3, True Result3, False)))

You can nest as many IF statements as you require but it can get messy.

Lookup Table
You can create a lookup table with the various available options from column A and the desired result. The formula syntax would then be:
=VLOOKUP(Column A value, Lookup Table, 2, False)

In each case the formulas would be in column B.
Matt PinkstonEnterprise Architect

Author

Commented:
If I go this route, how do I ensure that if there is no match that the resulting value is just blank
=IF(Condition1, True Result1, IF(Condition2, True Result2,IF(Condition3, True Result3, False)))

Also not sure how to check for starts with versus contains
Matt PinkstonEnterprise Architect

Author

Commented:
tried this one but syntax does not like it
=IF(LEFT(I2,7)="Growth-","Growth",LEFT(I2,7)="Portal-","Portal",LEFT(I2,11)="Compliance-","GRC",LEFT(I2,13)="Growth-Portal","Analytics","")
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Rob HensonFinance Analyst

Commented:
To overcome the no match scenario, cover all eventualities in conditions with the last False option being "" to return blank.

You can use wild cards to check for starts with and contains:

For example:
"Growth-*"   will signify starts with "Growth-"
"*Portal-Salesforce Connector*"   will signify contains "Portal-Salesforce Connector"

To reduce the number of conditions and subsequent IF statements you may be able to use a combination of AND or OR statements to define the condition. Likewise the sequential order of the IF statements would help determine the result.

With your second set of examples, by setting the IF statements in the same order as you have listed, the third condition will already exclude the items containing SalesForce or Microsoft because that condition has not yet been met in condition 1 or 2.

With the IF statement syntax, once a true condition has been met, the calculation will stop at that condition.
Rob HensonFinance Analyst

Commented:
You have missed out the IF statements:

=IF(LEFT(I2,7)="Growth-","Growth",IF(LEFT(I2,7)="Portal-","Portal",IF(LEFT(I2,11)="Compliance-","GRC",IF(LEFT(I2,13)="Growth-Portal","Analytics",""))))
Matt PinkstonEnterprise Architect

Author

Commented:
Thought I had it
=IF(LEFT(I2,7)="Growth-","Growth",IF(LEFT(I2,7)="Portal-","Portal",IF(LEFT(I2,11)="Compliance-","GRC",IF(LEFT(I2,13)="Growth-Portal","Analytics",""))))
Rob HensonFinance Analyst

Commented:
Or with wild cards as mentioned above:

=IF(I2="Growth-*","Growth",IF(I2="Portal-*","Portal",IF(I2="Compliance-*","GRC",IF(I2="Growth-Portal*","Analytics",""))))
Matt PinkstonEnterprise Architect

Author

Commented:
had it working with

=IF(LEFT(I2,7)="Growth-","Growth",IF(LEFT(I2,7)="Portal-","Portal",IF(LEFT(I2,11)="Compliance-","GRC",IF(LEFT(I2,13)="Growth-Portal","Analytics",""))))

it never hit the last if so I changed to

=IF(LEFT(I86,13)="Growth-Portal","Analytics",IF(LEFT(I86,7)="Portal-","Portal",IF(LEFT(I86,11)="Compliance-","GRC",IF(LEFT(I86, 7)="Growth-","Growth",""))))

now it is hitting none
Rob HensonFinance Analyst

Commented:
Correct, that is what I was referring to earlier with the order of the IF statements.
Matt PinkstonEnterprise Architect

Author

Commented:
But why is the later one above not working at all?

I went most specific first
Rob HensonFinance Analyst

Commented:
What is in I86 in that example?

Can you upload the file or at least a sample?
Matt PinkstonEnterprise Architect

Author

Commented:
Professional Services-Manage Services
Management
Professional Services-Customer Success
Professional Services-Manage Services
Professional Services-Manage Services
Professional Services-Manage Services
Professional Services-Manage Services
Professional Services-Customer Success
Professional Services-Customer Success
Professional Services-Manage Services
Professional Services-Customer Success
Professional Services-Customer Success
Professional Services-Manage Services
Professional Services-Customer Success
Professional Services-Manage Services
Professional Services-Customer Success
Professional Services-Customer Success
Portal-Analytics
Growth-Microsoft Sales App
Growth-Portal-Microsoft Connector
Growth-Salesforce Sales App
Growth-Portal-Salesforce Connector
Growth-Microsoft Sales App
Growth-Salesforce Sales App
Growth-Salesforce Sales App
Growth-Salesforce Sales App
Growth-Microsoft Sales App
Growth-Microsoft Sales App
Growth-Microsoft Sales App
Growth-Salesforce Sales App
Growth-Portal-Salesforce Connector
Growth-Salesforce Sales App
Portal-Analytics
Growth-Salesforce Sales App
Growth-Salesforce Sales App
Growth-Portal-Salesforce Connector
Growth-Salesforce Sales App
Growth-Salesforce Sales App
Management
Growth-Salesforce Sales App
Growth-Portal-Salesforce Connector
Growth-Salesforce Sales App
Portal-Analytics
Portal-Analytics
Growth-Salesforce Sales App
Portal-Analytics
Portal-Analytics
Portal-Analytics
Portal-Analytics
Portal-Analytics
Growth-Microsoft Sales App
Portal-Analytics
Growth-Microsoft Sales App
Compliance-Salesforce App
Compliance-Salesforce App
Compliance-Salesforce App
Compliance-Salesforce App
Technical Writing
Technical Writing
Technical Writing
Sales Operations
Sales Operations
Business Development-Marketing
Business Development-Marketing
Management
Administrative-HR
Management
Management
Administrative-Back Office
Administrative-Ops
IT Support
Administrative-Ops
Finance Analyst
Commented:
Copying from a list here doesn't always work as intended; spaces can become wrong type of space, dashes become hyphens etc etc.

Can you upload as an excel file?
Matt PinkstonEnterprise Architect

Author

Commented:
Thanks for all your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial