Index Collect Formula with Specific Text

NGordon

Hello,

We have one sheet with all of our projects in a column with various other columns of data associated. I am hoping to determine the best formula to look at all project names on this sheet and return them all to a column on new sheet if they have "Cliff" in the project name.

I have tried a few different ways and none seem to work. Below are the variables.

{PEC Blueprint Summary Sheet - DO NOT EDIT Range 1} = Name of the column on alternate sheet that has all project names listed.

[Column2]1 Column on new sheet to return Project Name

I believe that Index Collect may be the best formula, but are two different columns needed to return if value is found and then to copy?

Any help is greatly appreciated.

Monique_Odom_Comcast

Hello @NGordon,

For this, I would recommend a formula using the JOIN, COLLECT, and CONTAINS functions. Something like this should work:

=JOIN(COLLECT({PEC Blueprint Summary Sheet - DO NOT EDIT Range 1}, {PEC Blueprint Summary Sheet - DO NOT EDIT Range 1}, CONTAINS("Cliff", @cell)), ", ")

NGordon

@Monique_Odom_Comcast

This does pull all of the project names but keeps in one cell. Is there a way to return each into a different cell of a column?

Paul Newcome

Is there a reason you are using formulas in a sheet as opposed to a row report?

NGordon

@Paul Newcome

I want a list of projects which certain details but would also like to create additional commenting columns

Paul Newcome

You can include those columns in the source sheet and then use the report to show the filtered view.