Using Match with Regex and Array formula in Sheets-CodePudding

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

CodePudding user response：

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