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

Question

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!

Answer 1

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.