Array search by criteria to return found criteria

[GoofyShan]

I feel like I'm halfway to what I'm needing. Here's what I'm starting with:

[TABLE="width: 564"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Description
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]word list
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]The zookeeper was reading a book about a dog, bear and fish.
[/TD]
[TD]TRUE
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]aardvark
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]The girl has a cat.
[/TD]
[TD]TRUE
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]bear
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]The boy is allergic to dogs and cats.
[/TD]
[TD]TRUE
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]cat
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]The aardvark's closest relative is an elephant.
[/TD]
[TD]TRUE
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]dog
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]The animal presentation was at a library.
[/TD]
[TD]FALSE
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]elephant
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]fish
[/TD]
[/TR]
</tbody>[/TABLE]

$A is the text that I'm searching by $F criteria. $B return is from this formula:

=OR(ISNUMBER(SEARCH({"aardvark";"bear";"cat";"dog";"elephant";"fish"},A2)))

IF it is possible, what I would like is for the return of:

C2 dog, bear, fish
C3 cat
C4 dog, cat
C5 aardvark, elephant
C6 <blank>

Thanks in advance!!

 

[BenMiller]

Put this array formula in G2 and drag it across for as many options as you'll have in column F. So if there are six options, as in your example, drag it from G2 until L2, and then drag it down.

{=IFERROR(INDEX($F$1:$F$6,SMALL(IF(ISERROR(SEARCH($F$1:$F$6,$A2)),1E+99,1)*(ROW($F$1:$F$6)),COLUMN(A$1))),"")}

Remember to confirm it with CTRL+SHIFT+ENTER

The only thing with this formula is that it will return the items in the order of the original list in column F, NOT in the order in which they appear. So, for example, for the first sentence it will return bear, dog, fish - as opposed to dog, bear, fish.

 

[GoofyShan]

I had to go with the following to get the FISH included:

{=IFERROR(INDEX($F$1:$F$7,SMALL(IF(ISERROR(SEARCH($F$1:$F$7,$A2)),1E+99,1)*(ROW($F$1:$F$7)),COLUMN(A$1))),"")}

Thank you so much!!

 

[BenMiller]

No problem, glad I could help!