Finding a cell in a range containing the required fragment

[Ramzay]

I get a row of cells containing some text. Let's say they are names.

Mary Jane White

John Robert Black

Susan Lou Brown

Roger Green

Alice Ann Grey

Peter Red

<tbody>
</tbody>

With an Excel formula I need to get the whole content of the cell that contain a given word. For example, if I get "Ann", I should be able to return "Alice Ann Grey".

Any suggestions would be greatly appreciated. Thank you.

 

[63falcondude]

Try something along the lines of =INDEX(A1:F1,MATCH("*"&A5&"*",A1:F1,0))

where "Ann" is in A5

 

[Ramzay]

Try something along the lines of =INDEX(A1:F1,MATCH("*"&A5&"*",A1:F1,0))

where "Ann" is in A5

Thanks. That worked beautifully.

 

[AlKey]

Here is another way
Enter formula in B5 and copy down

=IF(A5="","",LOOKUP(1,-FIND($A5,A$1:F$1),A$1))

Book1
	A	B	C	D	E	F
1	Mary Jane White	John Robert Black	Susan Lou Brown	Roger Green	Alice Ann Grey	Peter Red
2
3
4
5	Ann	Alice Ann Grey
6	Black	John Robert Black
7	Gree	Roger Green
8	Sus	Susan Lou Brown
9	Red	Peter Red
Sheet5

 

[63falcondude]

Thanks. That worked beautifully.

You're welcome. Happy to help.