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 rather than trying to write a long, complex Excel function to do this, I would create a User Defined Function in VBA, which then can be used like any other Excel function, to do it.
Are you open to that kind of solution (one that makes use of VBA)?

 

[squeakums]

I think rather than trying to write a long, complex Excel function to do this, I would create a User Defined Function in VBA, which then can be used like any other Excel function, to do it.
Are you open to that kind of solution (one that makes use of VBA)?

I can write some vba code for this but was seeing if the formula was possible.

 

[Cubist]

Easiest way I can think of is to make a lookup matrix.

Book1
	A	B	C	D	E	F	G
1	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	Formula	Check
2	"FINRA" and "man"	FINRA - Leadership	Rooms to Go - FINRA-Manager	FINRA - Leadership	FINRA - Leadership	TRUE
3	"FINRA", <> "man"	FINRA - Execution	Rooms to Go - FINRA Associate	FINRA - Execution	FINRA - Execution	TRUE
4	Filter FINRA out, then search Manager	Leadership	Rooms to Go - Manager	Leadership	Leadership	TRUE
5	Executive Assistant	Admin	Shopping Executive Assistant	Admin	Admin	TRUE
6	Manager-FINRA	FINRA - Leadership	SPOC Manager-FINRA	FINRA - Leadership	FINRA - Leadership	TRUE
7	Officer	Leadership	Financial Officer	Leadership	Leadership	TRUE
8	"FINRA" and "Leader"	FINRA - Leadership	SPOC FINRA - Leader	FINRA - Leadership	FINRA - Leadership	TRUE
9	Administrative Assistant	Admin	SPOC Administrative Assistant	Admin	Admin	TRUE
10	If not of the above then	Execution	Rooms to Go Employee	Execution	Execution	TRUE
11
12
13		FINRA	man	leader	officer	Executive Assistant	Administrative Assistant
14	man	FINRA - Leadership	Leadership
15	leader	FINRA - Leadership		Leadership
16	FINRA	FINRA - Execution	FINRA - Leadership	FINRA - Leadership
17	officer				Leadership
18	Executive Assistant					Admin
19	Administrative Assistant						Admin
Sheet5

Cell Formulas
Range	Formula
E2:E10	E2	=IFERROR(TAKE(UNIQUE(TOROW(IF(SEARCH($B$13:$G$13,C2)*SEARCH($A$14:$A$19,C2),$B$14:$G$19),2),1),,1),"Execution")
F2:F10	F2	=E2=D2