Search a word in a text string in cells in a table

[Nordik]

Hi,

Let's say I have a result of a survey, but the answers got mixed up... some cells moved away form their column:

	Dog	Fish	Cat
Bob	I have a dog - blue	I have a fish - yellow	I have a cat - black
Carl	I have a cat - grey	I have a dog - red	I have a fish - black
John	I have a cat - red	I have a fish - red	I have a dog - green
Anna	I have a fish - blue	I have a dog - yellow	I have a cat - white

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I tried to sort this by using FIND, MATCH, INDEX but I seems I cannot make my formula work. For each line, I was trying to find "dog" for example and then display in a new column the content of cell in which "dog" is found. The final result I am looking for is:

	Dog	Fish	Cat
Bob	I have a dog - blue	I have a fish -yellow	I have a cat - black
Carl	I have a dog - red	I have a fish - black	I have a cat - grey
John	I have a dog - green	I have a fish - red	I have a cat - red
Anna	I have a dog - yellow	I have a fish - blue	I have a cat - white

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I don't need a formula to sort the whole table at once, I can do column by column/several steps.


Any help please?

Thanks in advance.

 

[RatExcel]

See if this works for you

https://app.box.com/s/c5cs8rbf5inq3ly47zce

 

[István Hirsch]

Give this formula a try (put it in e2 and copy it right to G2 and down as long as required):

=INDEX($B2:$D2,MATCH("*"&B$1&"*",$B2:$D2,0))

Excel Workbook
	A	B	C	D	E	F	G
1		dog	fish	cat	dog	fish	cat
2	Bob	I have a dog - blue	I have a fish - yellow	I have a cat - black	I have a dog - blue	I have a fish - yellow	I have a cat - black
3	Carl	I have a cat - grey	I have a dog - red	I have a fish - black	I have a dog - red	I have a fish - black	I have a cat - grey
4	John	I have a cat - red	I have a fish - red	I have a dog - green	I have a dog - green	I have a fish - red	I have a cat - red
5	Anna	I have a fish - blue	I have a dog - yellow	I have a cat - white	I have a dog - yellow	I have a fish - blue	I have a cat - white
Sheet

 

[Nordik]

Thanks to both of you.
Your solutions are working like a charm

=IF(IFERROR(FIND(LOWER(B$1);$B2);0);$B2;IF(IFERROR(FIND(LOWER(B$1);$C2);0);$C2;IF(IFERROR(FIND(LOWER(B$1);$D2);0);$D2;0)))

=INDEX($B2:$D2;MATCH("*"&B$1&"*";$B2:$D2;0))

I will go with István's formula as it was the one I tried to create. I wasn't using the * and the & in the correct way.

Thanks again