Search text in a string and return adjacent cell of first value found

[glynn1969]

Hello

I have a list in column A which will contain lots of various text entries (from A3 to A700) - within these cells there will be staff names, anywhere in column A and the same name could appear 1 or more times and amongst various other text

In column B will be a return to state their location (if a staff name does appear more than once they will always be against the same value in column B).

In cell C3 i will enter a staff name - my wish is to be able to place a formulae in cell D3 that will look through column A, find the first value of the name in cell C3 and return the location.

Column A Column B Cell C3 Cell D3
En John Smith 324334 Economics Paul Jones (need formulae to return "English")
Fr Paul Jones 324246 English
John Smith TE 8564645 Economics
GT John Smith 845754 Economics
HG Sandra Frey 671182 Accounts
KJ Paul Jones 85662 English

Any help would be much appreciated.

 

[jasonb75]

Please post your example with XL2BB so that the formatting is preserved and we can see what belongs where.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Fluff]

How about
=INDEX(B$2:B$20,MATCH("*"&C3&"*",A$2:A$20,0))

 

[Eric W]

Is this what you want?

Book1
	A	B	C	D	E
1	Data	Location		Name	Location
2	En John Smith 324334	Economics		Paul Jones	English
3	Fr Paul Jones 324246	English
4	John Smith TE 8564645	Economics
5	GT John Smith 845754	Economics
6	HG Sandra Frey 671182	Accounts
7	KJ Paul Jones 85662	English
8
Sheet37

Cell Formulas
Range	Formula
E2	E2	=LOOKUP(2,1/SEARCH(D2,A2:A10),B2:B10)

 

[glynn1969]

Is this what you want?

Eric W - that seems to work a treat - fantastic many thanks

 

[glynn1969]

How about
=INDEX(B$2:B$20,MATCH("*"&C3&"*",A$2:A$20,0))

Excellent Fluff....that works great...much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.

In future, please do not type you reply inside a quote, it makes look as though you have simply quoted somebody without a response.