Formula to assign a word based on certain words in a cell

[Some_Sand4232]

I need to assign a high level title based on different titles in a spreadsheet. I would like to use a formula that covers all of the following to assign titles. Is this possible?

For example if a cell contains the words:

Cell A contains Director of Engineering

Cell B has a formula that would return Hiring Manager




Based on criteria below.....



1.

Job Title Contains: Talent acquisition, talent management, talent, recruiting, recruitment, sourcing, executive recruiter

AND

Job Title Contains: VP, Vice President, President, Director, Head of, Lead

Return: Talent Acquisition Leader




2.

Job Title Contains: Talent acquisition, talent management, talent, recruiting, recruitment, sourcing, human resources, human resource

AND

Job Title Contains: Manager, Mgr, Management, Head of, Sup, Supervisor

Return: Recruiting Player-Coach




3.

Job Title Contains: Engineering, Software Development, Research and Development, RnD, R&D, Technology, Information Technology, Product, Product Development, Technology Operations, Programming

AND

Job title Contains: Director, VP, Vice President, Chief, Head of

Return: Hiring Manager




4.

Job Title Contains: Sourcing, Recruitment, Recruiting, Recruiter, Sourcer, Talent Acquisition, Talent, Human Resources, Human Resource, Technical

AND

Job Title Contains: Associate, Specialist, Recruiter, Coordinator

Return: Front Line Recruiter




5.

Job Title Contains: Diversity, Diversity and Inclusion, DEI, Inclusion

Return: DEI


Examples of what should be returned based on Title Column....

Title	Persona
Chief Technology Officer and Vice President Emerging Markets	Hiring Manager
Director - Autopilot Software	Hiring Manager
Director - Data & Analytics	Hiring Manager
Director of Engineering	Hiring Manager
Director Of Engineering	Hiring Manager
Director of New Programs Engineering	Hiring Manager
Director Software Engineering, Vehicle Firmware	Hiring Manager
Director, Autopilot Software	Hiring Manager
Director, Autopilot Software Engineering	Hiring Manager
Head of UI Design	Hiring Manager
Senior Director Of Engineering	Hiring Manager
Senior Director of Software Engineering	Hiring Manager
Tesla Director of Engineering - Automation Development Department	Hiring Manager
VP Software Engineering	Hiring Manager
Director Of Operations	Operations Lead
Head of Operations, Tesla Insurance	Operations Lead
Vice President of Operations	Operations Lead
Director of Global Recruiting Operations	Talent Acquisition Leader
Director of Talent Acquisition	Talent Acquisition Leader
Director of Talent Acquisition	Talent Acquisition Leader
Director, Recruiting & Workforce Development	Talent Acquisition Leader

6.

If title is none of the above, Return: Default

 

[Radoslaw Poprawski]

Vlookup or xlookup
xlookup is more dynamic