Check if strings in column start with any of the strings in an array?-CodePudding

I have a column containing strings. For each row in the column, I want to check if the string starts with one of several strings contained in an array. This array can contain any number of strings to check against the column.

The output I want is an array {TRUE;FALSE;TRUE;TRUE;etc} where it returns TRUE if the string begins with any of the strings contained in the array.

Anyone know how to do this? Thanks!

Edits for clarification: Office 365 (version 2109). I can't share the spreadsheet itself, but the column contains values like {1.1;1.1.1;1.1.2;1.2;1.3;etc}, and the arrays in question are things like {1.1;1.2}, so I want it to know which cells in the column begin with 1.1 or 1.2 in that case. So it's basically a hierarchically ordered list and I want to be able to pull into an array whether a given row of data is either a child of or is itself any of the items in the array.

Ideally it would be a formula only solution but I'm cool with VBA if it's necessary.

Example data

Bear in mind that in the real data there are many cells in column B which include way more than just two entries.

CodePudding user response：

Assuming the first entry to be checked is in A1:

=0 LEFT(A1,LEN(MyArray))=MyArray

placed somewhere within the worksheet, will produce a spill array of the same dimension as MyArray and comprising the required Booleans. I leave it to you to decide what to do with that resulting array.

CodePudding user response：

We can use MMULT and MATCH to return the correct array:

=LET(
    lkprng,   FILTER(A:A,A:A<>""),
    depcll,   B2&CHAR(10),
    depclm,   FILTERXML("<a><b>"&SUBSTITUTE(depcll,CHAR(10),"</b><b>")&"</b></a>","//b"),
    dep,      TRANSPOSE(depclm&""),
    mtch,     --(ISNUMBER(MATCH(LEFT(lkprng,LEN(dep)),dep,0))),
    mmt,      MMULT(mtch,SEQUENCE(COUNTA(dep))),
              TEXTJOIN(CHAR(10),,FILTER(lkprng,mmt>0,"")))