If/Then Contains Formula

[squeakums]

I am trying to figure out how to write and if/then contains formula.

If cell AD2 contains a what is below in the left column then place in current cell what is in right column? Like a vlookup off a contains formula.

Job Profile (contains)	Archetype
Risk Advis	Risk Advisory
Risk Program	Risk Program Execution
Governance & reporting	Governance & Reporting
Controls monitor, Issue validat	Controls Monitoring & Issue Validation
Business continuity	Business Continuity / Incident Management
Officer, reporting to Joseph Thompson	Sr. GCO
Other	Everything else

 

[Cubist]

Your question is vague. Can you show an example?

 

[saboh12617]

Hello,

If I understand correctly, supposing the table you posted is named Table1, and the value to be compared is in AD2, i think the formula below should work?

=XLOOKUP(AD2,Table1[Job Profile (contains)],Table1[Archetype],"Everything else",0)

Nota: You do not need the "Other" row at the end, it is handled by XLOOKUP 4th argument=default value.

 

[squeakums]

I am trying to figure out how to write and if/then contains formula.

 

[squeakums]

Hello,

If I understand correctly, supposing the table you posted is named Table1, and the value to be compared is in AD2, i think the formula below should work?

=XLOOKUP(AD2,Table1[Job Profile (contains)],Table1[Archetype],"Everything else",0)

Nota: You do not need the "Other" row at the end, it is handled by XLOOKUP 4th argument=default value.

I do not understand your code for the table, its just an array. How would I write it as an array?

 

[Cubist]

Try:

Book1
	A	B	C	D	E
1	Formula	Job Profile		Job Profile (contains)	Archetype
2	Risk Advisory	Sr. Risk Advisor Cap		Risk Advis	Risk Advisory
3	Risk Program Execution	Risk Program Execution III		Risk Program	Risk Program Execution
4	Controls Monitoring & Issue Validation	Controls Monitoring Mangager		Governance & reporting	Governance & Reporting
5	Controls Monitoring & Issue Validation	Issue Validation Manager		Controls monitor	Controls Monitoring & Issue Validation
6	Risk Advisory	Jr. Risk Advisor II		Issue Validation	Controls Monitoring & Issue Validation
7	Business Continuity / Incident Management	Business Continuity II		Business continuity	Business Continuity / Incident Management
8	Other	ETA Builder		Officer, reporting to Joseph Thompson	Sr. GCO
9	Other	Officer Of CEO
10	Risk Program Execution	Risk Program Manager II
Sheet2

Cell Formulas
Range	Formula
A2:A10	A2	=IFERROR(TOCOL(IF(SEARCH($D$2:$D$8,B2),$E$2:$E$8),2),"Other")

 

[squeakums]

Try:

Book1
	A	B	C	D	E
1	Formula	Job Profile		Job Profile (contains)	Archetype
2	Risk Advisory	Sr. Risk Advisor Cap		Risk Advis	Risk Advisory
3	Risk Program Execution	Risk Program Execution III		Risk Program	Risk Program Execution
4	Controls Monitoring & Issue Validation	Controls Monitoring Mangager		Governance & reporting	Governance & Reporting
5	Controls Monitoring & Issue Validation	Issue Validation Manager		Controls monitor	Controls Monitoring & Issue Validation
6	Risk Advisory	Jr. Risk Advisor II		Issue Validation	Controls Monitoring & Issue Validation
7	Business Continuity / Incident Management	Business Continuity II		Business continuity	Business Continuity / Incident Management
8	Other	ETA Builder		Officer, reporting to Joseph Thompson	Sr. GCO
9	Other	Officer Of CEO
10	Risk Program Execution	Risk Program Manager II
Sheet2

Cell Formulas
Range	Formula
A2:A10	A2	=IFERROR(TOCOL(IF(SEARCH($D$2:$D$8,B2),$E$2:$E$8),2),"Other")

Yes this works, thank you

 

[squeakums]

Try:

Book1
	A	B	C	D	E
1	Formula	Job Profile		Job Profile (contains)	Archetype
2	Risk Advisory	Sr. Risk Advisor Cap		Risk Advis	Risk Advisory
3	Risk Program Execution	Risk Program Execution III		Risk Program	Risk Program Execution
4	Controls Monitoring & Issue Validation	Controls Monitoring Mangager		Governance & reporting	Governance & Reporting
5	Controls Monitoring & Issue Validation	Issue Validation Manager		Controls monitor	Controls Monitoring & Issue Validation
6	Risk Advisory	Jr. Risk Advisor II		Issue Validation	Controls Monitoring & Issue Validation
7	Business Continuity / Incident Management	Business Continuity II		Business continuity	Business Continuity / Incident Management
8	Other	ETA Builder		Officer, reporting to Joseph Thompson	Sr. GCO
9	Other	Officer Of CEO
10	Risk Program Execution	Risk Program Manager II
Sheet2

Cell Formulas
Range	Formula
A2:A10	A2	=IFERROR(TOCOL(IF(SEARCH($D$2:$D$8,B2),$E$2:$E$8),2),"Other")

May I ask what if there a multiple contains is there a way to write a formula for this? for example

Job Profile (contains)	Clarifiers
"FINRA" and "man"	FIN - Leadership
"FINRA", does not contain "man"	FIN - Execution

 

[Cubist]

Show some examples of the Job Profiles in B.