0

Cell A1 contains a long text string. Cells B1:B6 contain various shorter text strings. Cells C1:C6 contain values.

I want to search the string in cell A1 for partial matches in any of the strings from range B1:B6, and for all such partial matches, return the corresponding value from range C1:C6.

=FILTER(C1:C6,ISNUMBER(SEARCH(A1,B1:B6))) returns the appropriate value(s) when one or more strings in range B1:B6 matches the string in cell A1 exactly, but returns no value in the case of a partial match. =FILTER(C1:C6,ISNUMBER(SEARCH(A1,"*"&B1:B6&"*"))) yields the same result.

Any tips on where to go from here would be appreciated.

Improve this question
4
  • Should be like this : =FILTER(C1:C6,ISNUMBER(SEARCH("*"&B1:B6&"*",A1)))
    – Mayukh Bhattacharya
    Commented Mar 19 at 0:02
  • Why it should be like the one I have suggested and not like yours because --> You are searching cells B1:B6 shorter text strings within Cell A1, so here, the first arg of SEARCH() say, find_text (B1:B6), within_text (A1). returns the position of the character within the string, if returned then wrap within ISNUMBER() to return TRUE and wrap with FILTER() to return the C1:C6 based on the include
    – Mayukh Bhattacharya
    Commented Mar 19 at 0:13
  • 1
    Egad, you're right. I was so close, and yet so far. :)
    – foolishgrunt
    Commented Mar 19 at 17:13
  • You can post this as an answer as well!
    – Mayukh Bhattacharya
    Commented Mar 20 at 18:51

1 Answer 1

Reset to default
0

Correct formula should be:

=FILTER(C1:C6,ISNUMBER(SEARCH("*"&B1:B6&"*",A1)))

I simply had the "find text" argument and the "within text" argument reversed inside the search function.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .