Searching a list for all returns with a selected sequence

Robert Schulingkamp

Hello,

I'm new to Smartsheet and started off by using Smartsheet to make a searchable sheet to look up the definition of acronyms or abbreviations. I have one sheet as a source with 2 columns; 1st column is the abbreviations or acronyms the 2nd column is the respective definition. See sample below.

In Excel I can enter a couple letters and get a list of acronyms with those letters in it.

Using: IF(B3="","",FILTER(Acronym!A:B,ISNUMBER(SEARCH(B3,Acronym!A:A)),"No Results!"))

A search of "aaa" yields this below:

But in Smartsheet using a Lookup sheet called "FDA Acronym Look up" to search a separate data sheet called "FDA Acronym" with the formula:

=IF(Acronym@row = "", "", (JOIN(COLLECT({FDA Acronym Range 3}, {FDA Acronym Range 2}, Acronym@row), (SUBSTITUTE([Carriage Return]$2, "-", "")))))

I get this below:

Only exactly the letters used. Is there a way, like Excel, to get all the acronyms that contain the searched letters?

Thanks Bob

Mike TV

@Robert Schulingkamp

One thing that SmartSheet is notoriously bad at is using a formula to build a list of results. SmartSheet wants to provide you with just a single result per formula. One work around I've used, that the vast majority of people hate and won't take the time to create in a sheet, is to use an INDEX(COLLECT formula and increasing the row_position value on each formula.

For example, you have to pre-determine the maximum number of results you could receive. Such as maybe 10 or 20 for what you're doing. Possibly more. Then you create a search sheet with something like this:

=INDEX(COLLECT({Range 1}, [Search Value]1), 1)

Something like this will search that provided range for the provided criteria and spit out the first result it gets. Then you'd put the same formula on the next row of your search sheet with a ,2 so it will spit out the 2nd result it gets...and so on.

Then when you've got those 10-20 or however many formulas set up you just enter your search value in the field you've provided on the search sheet and it will automatically fill in the results.

This takes a little set up but it does work. It's just not glorious and has some scalability issues when you hit the maximum number of results you've setup the sheet for and have to add more down the road.

Robert Schulingkamp

Hi Mike,

Thanks for your response, but perhaps I wasn't clear to my need. Unless I'm using it wrong, the formula you suggested only returns a value that exactly matches the search criteria. This is similar to one I am currently using (above), except the one I am using will return ALL the matches w/o needing to create multiple formulas on successive rows (see below). What I was looking for was a way to return any result that has the search criteria within the acronym. Sort of using wildcards before and after " *aaa* ". If you look at the top of this post you'll see in Excel I did a search on "aaa" and got in addition to the exact match I also got the results with "aaa" in the beginning and end, but I can't make Smartsheet do this.

Thanks

Bob