Searching multiple sheets for ALL instances of a keyword, not just the first.

smeyers

I have multiple sheets that outline course topics and keywords by chapter (topics go from top to bottom in a cell). I want to build a sheet where I can type in a keyword and have returned all of the cell addresses for each course where the keyword appears in each of the course outlines. I can INDEX/MATCH, but can only get the first instance to show. How do I return all of the instances (cell addresses) where a keyword is found in a column?

In other words, if I search for "student" in a cell, I want to return all of the cell addresses in the topic column for Course A, Course B, Course C, etc. where "student" can be found.

Paul Newcome

Are you able to provide some screenshots for context? Sounds as if you are going to want a JOIN or INDEX/COLLECT with a CONTAINS function and possibly a helper column.

smeyers

Here is an example of what I am looking to do. I want to type in a keyword in the gray box. I would like the formula to search other sheets with course layouts in them for that keyword. In the return columns below, I would like the page locations from the referenced sheets where the keyword is found (in this case TargetZone Activity). The results can be joined in one cell or just listed in separate cells underneath each other (both options are shown below).

In the screenshot below of Course 1, the keyword would be matched in the Primary Column and the page location from Column 2 would be returned to the master spreadsheet.

In the screenshot below of Course 2, which is just a copy of Course 1 for now, the keyword would be matched in the Primary Column and the page location from Column 2 would be returned to the master spreadsheet. This would occur for several courses.

We are basically trying to find where, in several courses, specific topics are located without having to search each one individually. In a perfect world, we wouldn't have to type the exact matching phrase, we could search for any topic containing the keyword. Example: searching "Activity" would also return the page locations 8 and 16 because "Activity" is contained in the cell.

Hope this makes sense. Thanks for helping! I'm new to Smartsheet, but have used formulas in Google Sheets fairly often.

Sam

Paul Newcome

I would suggest a JOIN/COLLECT along the lines of

=JOIN(COLLECT({Source Sheet Page # Column}, {Source Sheet Title Column}, CONTAINS("Activity", @cell)), ", ")

Paul Newcome

You could also use a row report, but users would have to be shared to all underlying sheets with at least read only permissions and would need admin access to the report to be able to adjust the filter.

smeyers

Perfect! Just what I needed. Thank you!🙌

Paul Newcome

Happy to help. 👍️