# search array of strings inside other string

I am trying to determine if any of the individual strings in an array/table of strings (i.e. Sheet2 A1:A4) is a substring in another cell (Sheet1 A1).

     Sheet1
A
1 "Letter to Bob"

Sheet2
A
1  Sam
2  Bill
3  Bob
4  Dick


Are any of the strings in Sheet2 A1:A4 inside the Sheet1 A1 string?? (In this example, the answer is Yes.. Bob is found in Sheet1 A1)

What would the formula to accomplish this look like??

edit retag close merge delete

Not sure what information you really want to have: Check

=IFNA(MATCH(1;NOT(ISNA(REGEX(Sheet1.$A$1;A1:A4)));0);"no match"), which provides either the number (i.e. the index) of the first name in A1:A4 to appear in Sheet1.A1 (here: 3) or "no match", if no name does appear in Sheet1.A1

( 2020-11-20 11:57:50 +0100 )edit

Thank you. I just need a boolean if one was found. Thank you... I will examine your example to better understand your solution.

( 2020-11-20 12:18:53 +0100 )edit

Sort by » oldest newest most voted

Hello,

you can use the formula by slightly modifying "no match" to 0

=IFNA(MATCH(1;NOT(ISNA(REGEX(Sheet1.$A$1;A1:A4)));0);0)

Note All values >=1 are boolean TRUE.

Hope that helps.

more

Excellent... thank you, Opaque, for you time and effort in helping me with this.

( 2020-11-20 14:48:26 +0100 )edit

If the answers works for you, please consider to click the check mark (✔) next to the answer. Thanks in advance ...

( 2020-11-20 14:54:10 +0100 )edit

From Opaque's suggestion, I realized it would be easier to use Regular Expressions with just strings, rather than trying to make it work with an array of cells. So this is my live solution. Thank you Opaque.

=IF( ISBLANK( K$4 ), 0 ,IF( ISNA(REGEX( CONCAT($C69:$D69) , K$4 ) ) , 0, $E69 *-1 ) ) I am using it to examine a bank statement. When I find a specific string in the transaction description, then the transaction amount get's displayed. This way it generates an expense report. Columns C:D contain the transaction description Cell K$4 contains the regular expression
Column E contains the transaction amount

If Cell K\$4 is blank, that means it has not yet been defined, so it is ignored

more

Hello,

I wrote something that might be kind of helpful to you a little while back