Multiple If/then formula if contains

[squeakums]

I have this basic formula:

=IF(cell="text", value_to_return, "").

Id like to write a formula that is more like =if(cell="FINRA" and "man", "Leadership), if(cell="FINRA" and <> "man", then "Execution", if(cell=<>"Finra" and "Manager, "leadership), if(cell="executive assistant", "admin") otherwise "Execution".

How would I write this multiple if/then statement for containing words please?

 

[Joe4]

I think there are some logic problems with your mock-formula, the way you structured it (it is not a valid nested formula format, at least not one that makes any logical sense). So we really cannot tell what exactly you want to happen.

Can we come at this a different way, and can you provide a list that shows what value should be returned in which conditions?

For example:

Situation	Conditions	Return
1	Column A is "Man" and Column B is "Dog"	abc
2	Column A is "Man" and Column B is "Cat"	lmn
3	Column A is "Child" and Column B is "Cat"	xyz

 

[squeakums]

I think there are some logic problems with your mock-formula, the way you structured it (it is not a valid nested formula format, at least not one that makes any logical sense). So we really cannot tell what exactly you want to happen.

Can we come at this a different way, and can you provide a list that shows what value should be returned in which conditions?

For example:

Situation	Conditions	Return
1	Column A is "Man" and Column B is "Dog"	abc
2	Column A is "Man" and Column B is "Cat"	lmn
3	Column A is "Child" and Column B is "Cat"	xyz

This is looking at one cell. I used an array before but this one might not work with that array because it has multiple contains:

If Contains this in the cell D2	What it should Create for the Formula Cell
"FINRA" and "man"	FINRA - Leadership
"FINRA", <> "man"	FINRA - Execution
Filter FINRA out, then search Manager, <> contingent	Leadership
Executive Assistant	Admin
Manager-FINRA	FINRA - Leadership
Officer	Leadership
"FINRA" and "Leader"	FINRA - Leadership
Administrative Assistant	Admin
If not of the above then	Execution

 

[Scott Huish]

You only reference 1 cell in that chart. Are you looking for each of those words mixed with other text?

 

[squeakums]

You only reference 1 cell in that chart. Are you looking for each of those words mixed with other text?

Correct, it is several lookups in the cell to determine which bucket it falls under. Then, the formula is filled down.

 

[Joe4]

Thank you. It sounds like we are doing partial matches then, right?
Just to confirm that, for each item in the list, can you show us a complete entry in cell D2 would look like?

So, maybe the first one looks like:
"I am a man who works for FINRA".
and the second may look like:
"I work for FINRA".

I see some potential issues where you could have overlaps (entries that meet the criteria for two or more items in your list).
For example, an entry like:
"I am the Leader of FINRA"
meets the criteria for both of your rules:
"FINRA", <> "man"
"FINRA" and "Leader"

If that is the case, is there some sort of hierarchy (where certain rules/conditions take precedence over others)?

 

[squeakums]

Thank you. It sounds like we are doing partial matches then, right?
Just to confirm that, for each item in the list, can you show us a complete entry in cell D2 would look like?

So, maybe the first one looks like:
"I am a man who works for FINRA".
and the second may look like:
"I work for FINRA".

I see some potential issues where you could have overlaps (entries that meet the criteria for two or more items in your list).
For example, an entry like:
"I am the Leader of FINRA"
meets the criteria for both of your rules:
"FINRA", <> "man"
"FINRA" and "Leader"

If that is the case, is there some sort of hierarchy (where certain rules/conditions take precedence over others)?

They aren't sentences, they are long job titles that we are trying to bucket. So, it would either be xxxxxx..xxxxx finra leader or xxxxx.xxxxx finra manager or sometimes spelled finra manger b/c space runs out.

 

[Joe4]

Please show me examples of each one, like I asked.

 

[squeakums]

Please show me examples of each one, like I asked.

 

[squeakums]

Here you are:

If Contains this in the cell D2	If it finds this contain what formula produces	Field D2 its searching for text	If formula was in this cell what it would produce; Results
"FINRA" and "man"	FINRA - Leadership	Rooms to Go - FINRA-Manager	FINRA - Leadership
"FINRA", <> "man"	FINRA - Execution	Rooms to Go - FINRA Associate	FINRA - Execution
Filter FINRA out, then search Manager	Leadership	Rooms to Go - Manager	Leadership
Executive Assistant	Admin	Shopping Executive Assistant	Admin
Manager-FINRA	FINRA - Leadership	SPOC Manager-FINRA	FINRA - Leadership
Officer	Leadership	Financial Officer	Leadership
"FINRA" and "Leader"	FINRA - Leadership	SPOC FINRA - Leader	FINRA - Leadership
Administrative Assistant	Admin	SPOC Administrative Assistant	Admin
If not of the above then	Execution	Rooms to Go Employee	Execution
Advisor II	Execution