# 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!

• Welcome. Have you solved your issue? Still interested in a solution? Jan 29, 2021 at 6:23

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.