How can I check if any of the values in a column with comma seaparated values are in another table in Excel?

Question

I have a table like so:

IDs	data
one, two	a
three, four one	b
five, six	c
seven	d
eight, nine, three	e

I have another table like so:

ID
one
four
six
nine

I need to add a column to the first table that will tell me if any of the values in the IDs column are in the ID column of the other table.

If it matters, both ables have 200,000+ rows.

I am on Microsoft Office Professional Plus 2021. It does not have many of the newer functions like TEXTSPLIT. :(

Answer 1

Match Delimited

• In the first (data) row of column Found use:

  =COUNT(XMATCH(TEXTSPLIT([@IDs],", "),Table2[ID]))>0
  

Or:

=LET(data,[@IDs],sdlm,", ",ddlm,"-",ignore_blanks,1,if_not_found,NA(),
    d,TEXTSPLIT([@IDs],sdlm),
    m,XMATCH(d,Table2[ID]),
IF(COUNT(m)=0,if_not_found,
    TEXTJOIN(ddlm,ignore_blanks,FILTER(d,ISNUMBER(m)))))

Answer 2

My approach is here..

=LOOKUP(2^55,SEARCH($F$5:$F$8,SUBSTITUTE(B5,",","")),$F$5:$F$8)

you can use IFERROR function to eliminate #NA