Help expanding on this forumla for what I need

[Narrian]

=IF(ISNUMBER(SEARCH("Find_Text",H2)),"Value_if_True","")

Purpose, I need it to query a list of names not just 1 name, and output 2 different results based on a match. So for sake of example Employee names and then divided up into two different job roles. I'm sure Xlookup if going to be the route i'll have to take on this but I am lost as I am a beginner with excel formulas.

Any help would be greatly appreciated, thank you!

 

[awoohaw]

Do you want to query "John Smith" and find where Column with "John" and Column with "Smith" matches in the "Job Role" column?
Are all names only two names? What if there are initials or a middle name, a prefix or a suffix? what if the person has two job roles?

the most basic of these would be

=XLOOKUP(TextBefore("Full Name"," ")&TextAfter("Full Name"," "),FirstNameColumn & LastNameColumn, JobRoleColumn,0)

Book1
	A	B	C	D	E	F
1	Name:	John Smith
2	Role:	CEO		John	Jones	Manger
3				John	Smith	CEO
4				Bill	Smith	Laborer
Sheet1

Cell Formulas
Range	Formula
B2	B2	=XLOOKUP(TEXTBEFORE(B1," ")&TEXTAFTER(B1," "),D2:D4 & E2:E4, F2:F4,0)

 

[Narrian]

Do you want to query "John Smith" and find where Column with "John" and Column with "Smith" matches in the "Job Role" column?
Are all names only two names? What if there are initials or a middle name, a prefix or a suffix? what if the person has two job roles?

Only using First names, for this purpose they would only have 1 job code assigned. Column H as it stands now is conditionally formatted with a name list drop down, and are selected manually, I'm just looking for an output for Column D between the two "Roles".

 

[awoohaw]

then change the above to:

=XLOOKUP(TEXTBEFORE(B1," "),D2:D4, F2:F4,0)

sorry my columns don't match yours. You'll need to edit them.


Also, this only gives one result. What is the 2nd you seek?

 

[Narrian]

Thank you I will play around with this when I get back to my computer.