Cross reference in Google Sheets (2 sheets with varying length columns and potential duplicates)

Question

First off, many thanks in advance! This is my first post. I am brand new to google sheets as well.

My data is the following. One sheet contains a column of last names only. The last names columns does include duplicate last names. My main sheet contains a column of first and last names which is about 3x as long as the list of last names only.

I would like to generate a boolean column for pivoting purposes which cross references the last name list with the full names. If any of the last names are partially matched to a full name, then the boolean is a 1 or TRUE.

Cross Reference:  Doe & Mann

Jim Smith
0
Jane Doe
1
John Rich
0
Joe Bills
0
Brian The Mann
1
Bill Downs
0

Alistair McEvoy Alistair McEvoy · Accepted Answer · 2021-08-31T16:31:16

=ArrayFormula(REGEXMATCH(A1:A11, TEXTJOIN("|", TRUE, C1:C11)))

Paste this in the column where you want TRUE/FALSE. Replace A1:A11 with your full names list, C1:C11 with your cross-reference.

If you would like empty spaces, or customize the response, use:

=ArrayFormula(IF(REGEXMATCH(A1:A11, TEXTJOIN("|", TRUE, C1:C11)), "TRUE TEXT", "FALSE TEXT"))

Cross reference in Google Sheets (2 sheets with varying length columns and potential duplicates)

2 Answers