# Check if a string appears within another cell, and return the longer of the 2

I am having a hard time thinking of an formula that can help me pull the data needed in this example:

In the C1 cell, I would like to make a formula that does this:

1. Check if any cells in the A column contain the value that appears in B1
2. If it finds a match, return the value from the A column but only if it is longer than the value that is already in B1
3. If there is no such match, return the value of B1 again

So in this example, it should return "Bob Jones"

Any help would be greatly appreciated!

You could do something like this:

``````=IFERROR(ARRAY_CONSTRAIN(FILTER(
QUERY(A:A, "SELECT A WHERE A CONTAINS '"&\$B1&"'"),
LEN(QUERY(A:A, "SELECT A WHERE A CONTAINS '"&\$B1&"'"))
= MAX(LEN(QUERY(A:A, "SELECT A WHERE A CONTAINS '"&\$B1&"'")))
), 1, 1), B1)
``````

So we `QUERY` the `A` column for values that equal the value at `B1`.

Then we can filter all matches so we only get the maximum length match.

If there are errors the assumption is that there was no match and we return the value at `B1`.

The result is constrained to a single cell result using `ARRAY_CONSTRAIN`. If there are multiple matches with the same number of characters the first match is used.

Caveat to this approach is that column `A` needs to be formatted as plain text or `QUERY` won't work.

``````=LET(data,{B1; A:A},
arr,SORT(data, INDEX(LEN(data)), 0),
SORTN(arr,1,0, REGEXMATCH(arr, B1),0))
``````
1. `data` stores your lookup range `A:A` as well as your lookup value `B1`. By including `B1` in `data` it is simple to return it of its the longest (or only) matching value.
2. `arr` stores an array returned by sorting `data` in descending order by number of characters using LEN.
3. All that remains is to use SORTN to return the first result after sorting the values based on whether REGEXMATCH returns true for the value.
4. You could alternatively use FILTER and INDEX instead of SORTN:
``````=LET(data,{B1; A:A},
arr,SORT(data, INDEX(LEN(data)), 0),
INDEX(FILTER(arr, REGEXMATCH(arr, B1)),1))
``````

# Multi-row Versions

If you need to repeat the process for multiple rows in column B you can adapt the formula to return all the values.

``````=BYROW(TOCOL(B:B, 1), LAMBDA(r, LET(
data,{r; A:A},
arr,SORT(data, INDEX(LEN(data)), 0),
SORTN(arr,1,0, REGEXMATCH(arr, r),0))))
``````
``````=BYROW(TOCOL(B:B, 1), LAMBDA(r, LET(
data,{r; A:A},
arr,SORT(data, INDEX(LEN(data)), 0),
INDEX(FILTER(arr, REGEXMATCH(arr, r)),1))))
``````