Search Cells in Column For Value and Return All Instances Of It

[mdsurf]

Hi there, I have a spreadsheet with a lot of data. I'm trying to search cells in a column to see if specific info is present and then return all values in another correlated cell.

Here is an example of the data

Data	Misc Data	SKU
I like to go sailing on a boat on the weekends	Misc data 1	23DSF33JY
My dad loves to play soccer	Misc data 2	4567HGDDD22
Fishing while sailing on a boat is the best	Misc data 3	CV567FGH

What I need to do is search the first column for a specific value, in this case the words "sailing on a boat" and return every SKU where those words are present.

sailing on a boat

23DSF33JY

CV567FGH

As you can see, I have the term that needs to be searched for, and the corresponding SKU. The number of SKUs that will be returned will vary and the list of Data is quite long.

Thank you in advance for any help.

 

[Fluff]

What version of Excel are you using & on what platform?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mdsurf]

Wanted to check back to see if anyone had any ideas here. Thank you!

 

[Fluff]

Yes I do, but you haven't answered my questions, nor have you updated you account details.

 

[mdsurf]

@Fluff

Sorry about that! I updated my profile details, and to answer your questions

The version I am using is Microsoft Excel for Mac Version 16.16.27

I do also use Google sheets sometimes as well if that matter. Sorry I'm a bit of an excel newb, hence why I'm here asking questions

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1	Data	Misc Data	SKU		sailing on a boat
2	I like to go sailing on a boat on the weekends	Misc data 1	23DSF33JY		23DSF33JY
3	My dad loves to play soccer	Misc data 2	4567HGDDD22		CV567FGH
4	Fishing while sailing on a boat is the best	Misc data 3	CV567FGH
5
6
Main

Cell Formulas
Range	Formula
E2:E4	E2	=IFERROR(INDEX($C$2:$C$4,AGGREGATE(15,6,(ROW($C$2:$C$4)-ROW($C$2)+1)/(ISNUMBER(SEARCH(E$1,$A$2:$A$4))),ROWS(E$2:E2))),"")