Using Match with Regex and Array formula in Sheets

Question

I have a list of names for which I want to know if there's a cross match in family name. 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

because I just want to know which are the ones for which all members have fam. names. For Scott only Noah Scott is a match - so I guess I could fine tune it to show one match or smth., if there is just one but at this point I just wanna know if all are match . I'm using col. B Name just as reference for the fam. name. — Debora S.
– Debora S., Commented Oct 12, 2021 at 16:59

player0 · Accepted Answer · 2021-10-12 18:17:47Z

1

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")))

answered Oct 12, 2021 at 18:17

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kishkin Over a year ago

Sand family looks at you with confusion. :)

mohagali · Accepted Answer · 2021-10-12 17:27:07Z

0

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

answered Oct 12, 2021 at 17:27

mohagali

4104 silver badges11 bronze badges

Comments

kishkin · Accepted Answer · 2021-10-14 13:58:22Z

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"
  )
)

Collectives™ on Stack Overflow

Using Match with Regex and Array formula in Sheets

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related