Using Match with Regex and Array formula in Sheets

user_16851383 0
Viewed 150

I have a list of names for which I want to know if there's a cross match in family name. SEE EXAMPLE So if all in Family column contain family name (as the one in col B) - there'd be a Match, otherwise not. I started by cleaning/splitting the names

=TRANSPOSE(ARRAYFORMULA(TRIM( SPLIT(SUBSTITUTE($A2," and",","),","))))

then doing a T/F match of only the family name for each case

=ISNUMBER(MATCH(REGEXEXTRACT($B$2,"\w+$"),REGEXEXTRACT(D2,"\w+$"),0))

I wanted to do this MATCH as an array, but it's not working. And then I'd have to do a count of the TRUE value if all are TRUE return a MATCH, else NO MATCH. I obviously want to do this in a single cell, but got stuck because I can't make the MATCH an array. I hope that makes sense, or am I going about this the wrong way. Here's the sample sheet

array-formulas transpose google-sheets match regex
3 Answers

try:

=ARRAYFORMULA(IF(A2:A="",,IF(1+LEN(
 REGEXREPLACE(SUBSTITUTE(A2:A, "and", ","), "[^,]", ))=
 MMULT(N(IFERROR(IF(SPLIT(SUBSTITUTE(A2:A, "and", ","), ",")="",,
 REGEXMATCH(TRIM(SPLIT(SUBSTITUTE(A2:A, "and", ","), ",")), 
 REGEXEXTRACT(B2:B, "\w+$"))))), 
 SEQUENCE(COLUMNS(SPLIT(SUBSTITUTE(A2:A, "and", ","), ",")), 1, 1, 0)), 
 "match", "no match")))

enter image description here

user_56326290

use this

C2=trim(index(split(B2," "),1,COUNTA(split(B2," "))))

D2=SUBSTITUTE(A2,"and",",")

E2=if(COUNTA(split(D2,C2,false))=counta(split(D2,",",false)),"matched","not matched")

1- C2 gets the last word from sentence as last name

2- D2 Replaces "and" by ","

3- E2 splits D2 by "," and splits D2 by C2 then counts and compares if same means all matched

Result

enter image description here

user_29160900

another one for you:

=ARRAYFORMULA(
  IFS(
    A2:A = "",,
    ISNA(MATCH(
      ROW(A2:A),
      QUERY(
        QUERY(
          SPLIT(
            FLATTEN(
              FILTER(
                  ROW(A2:A) & "♥"
                & --NOT(REGEXMATCH(
                      SPLIT(
                        REGEXREPLACE(A2:A, ",\s*|\s+and\s+", "♥"),
                        "♥"
                      ),
                      "^$|" & REGEXEXTRACT(B2:B, "\s(\w+)$")
                    )),
                A2:A <> ""
              )
            ),
            "♥"
          ),
          "SELECT Col1, SUM(Col2)
           GROUP BY Col1",
        ),
        "SELECT Col1
         WHERE Col2 = 0",
      ),
    )),
      "NO MATCH",
    True,
      "MATCH"
  )
)

enter image description here

user_2798060
Related