2

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

enter image description here

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!

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

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.