Search Cell For A Value and Returning Another Value

[mdsurf]

Hi there, I have a list of peoples names and I want to search a bunch of cells and when someones name is present return a specific value. Let me explain below:

TAB1

Name	Jobs
Sarah Smith	Cook
John Doe	Bar Back
Erin Link	Waitress
Mark Wall	Manager

I then want to search for those names in a column and return the job if the name is found in the cell. The cells will have additional text though so it will have to be a partial match.

TAB2

Text to search for Name	If Name present return Jobs and where I need help writing excel formula
part time, Sarah Smith, weekends	Cook
full time, Mark Wall, salaried, on duty	Manager
Erin Link, new hire, full time, week, morning shifts	Waitress
part time, summer season, Josh Robinson, salary	N/A

As you can see, trying to search to see if a name is present in a cell and if it is, return the job associated with the name. Any help is much appreciated, thank you!

 

[Toadstool]

Hi Mdsurf,

Does this do what you want?

Mdsurf3.xlsx
	A	B
1	Name	Jobs
2	Sarah Smith	Cook
3	John Doe	Bar Back
4	Erin Link	Waitress
5	Mark Wall	Manager
6
TAB1

Mdsurf3.xlsx
	A	B
1	Text to search for Name	If Name present return Jobs and where I need help writing excel formula
2	part time, Sarah Smith, weekends	Cook
3	full time, Mark Wall, salaried, on duty	Manager
4	Erin Link, new hire, full time, week, morning shifts	Waitress
5	part time, summer season, Josh Robinson, salary	N/A
6
TAB2

Cell Formulas
Range	Formula
B2:B6	B2	=IF(A2="","",IFERROR(INDEX('TAB1'!$B$2:$B$9999,AGGREGATE(15,6,ROW('TAB1'!$A$2:$A$9999)-ROW('TAB1'!$A$1)/(('TAB1'!$A$2:$A$9999<>"")*(ISNUMBER(SEARCH('TAB1'!$A$2:$A$9999,A2)))),1)),"N/A"))