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!

0

2 Answers 2

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.

0
=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)))) 

Your Answer

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

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