Find text in cell and return values in array

[zjamespryor]

Hello,

I have a sheet that needs to search for text in a column, and then return the corresponding value next to it. I need the results stacked together so that there aren't blank rows between results. I can't use VBA for this as I'm actually doing it in Google Sheets. Thank you in advance for any help you can offer!

Array Help.xlsx
	A	B	C	D	E	F	G	H	I
1
2		Person (Desired results)		Lookup ->	Dog			Data Validation
3		James						Dog
4		Dan						Cat
5								Horse
6								Pigeon
7
8								Person	Animals
9								James	Horse, Dog
10								Sarah	Cat, Horse
11								Dan	Dog
12								Aaron	Pigeon
13								Jeff	Cat, Pigeon
Results Page

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$H$3:$H$6

 

[Fluff]

When posting questions about Google Sheets, you should post them in the General Discussion & Other Applications section of the board.
I have moved it for you this time.

 

[zjamespryor]

When posting questions about Google Sheets, you should post them in the General Discussion & Other Applications section of the board.
I have moved it for you this time.

Thank you, I realized my mistake in posting to the wrong forum shortly after.

 

[Fluff]

How about

=FILTER(H9:H13,ISNUMBER(SEARCH(E2,I9:I13)))

 

[zjamespryor]

How about

=FILTER(H9:H13,ISNUMBER(SEARCH(E2,I9:I13)))

Works like a charm. I've been smacking myself for almost 3 hours coming up with all sorts of long complicated formulas that have not worked. Simple and effective. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.