Partial match with INDEX(DISTINCT(COLLECT(

l.gann

Hello,

I need to collect a list of values from my source sheet based on a search field (cell) in my target sheet. I have a very large subset of data that I'm pulling from, so I've divided it into 6 sheets to increase the speed of the lookup.

Currently, I'm using the formula: =IFERROR(IF(OR(LEFT([Lookup Value]$2, 1) = "A", LEFT([Lookup Value]$2, 1) = "B", LEFT([Lookup Value]$2, 1) = "C"), INDEX(DISTINCT(COLLECT({AttendeesConcat}, {A-C Last Name}, =[Lookup Value]$2)), [Primary Column]@row), ""), "")

This works, but you have to enter the full search term (last name). I'm trying to find a way to alter the formula to collect the cells based on a partial match.

Example: User enters Ad in the search field and the formula returns any values that contain Ad (Best case scenario would be if we can pull the results that start with the text string entered).

For the ideal solution, "Ad" would pull Adams, Adrien, Adios, etc.

Target Sheet:

Source Sheet: Trying to pull data from the Concatenated ID Column

Thank you!

jmyzk_cloudsmart_jp

Hi @l.gann

Here's my solution.

1. Determining if a Name Starts with Certain Characters in Smartsheet

We need to determine if the target name starts with certain characters.

Unfortunately, Smartsheet does not have the equivalent of Python' "startswith()" or Java Script's "startsWith()" methods. 

Method 1: Using LEFT and LEN functions.

=IF(LEFT([Full Name]@row, LEN([Look Up Value]@row)) = [Look Up Value]@row, true, false)

Method 2: Using the FIND function.

=IF(FIND([Look Up Value]@row, [Full Name]@row) = 1, 1, 0)

The difference is that the Find method is case-sensitive so this method would be more appropriate here.

2. Selecting from Multiple Candidates:

Since partial match gives you multiple candidates, you must determine how users select from the candidates.

1. Concatenate the results using the CHAR(10) delimiter in a column.
2. Place selection numbers in another column on the left side of the first.
3. Use the selected number with the INDEX function to fetch the desired result.

3. Demo Solution with Employee Data:

The demo solution dashboard demonstrates the above with an example of employee data.

Since the data is an employee directory, we do not have to use the DISTINCT function, but the demo works even with the function.

The yellow cells are editable, so please check how the formulas work.

https://app.smartsheet.com/b/publish?EQBCT=8b4e783097bd43548953c17bf26bb365

Formulas

Result List:

=JOIN(DISTINCT(COLLECT({Full Name}, {Full Name}, FIND([Look Up Value]@row, @cell) = 1)), CHAR(10))

Result Count

=COUNT(DISTINCT(COLLECT({Full Name}, {Full Name}, FIND([Look Up Value]@row, @cell) = 1)))

# (the selection number column)

=JOIN(COLLECT({Index Number}, {Index Number}, <=[Result Count]@row), CHAR(10))

Selected

=IF(Selection@row > 0, INDEX(DISTINCT(COLLECT({Full Name}, {Full Name}, FIND([Look Up Value]@row, @cell) = 1)), Selection@row))

If you need to access the demo contents, please complete the following form;

https://app.smartsheet.com/b/form/c06619d504f9418284b4125eec205a54

(We create a copy of the contents folder and share the contents in the folder. Note: We can not share the folder.)