How to check if two cells in the same column contain matching text in Excel?

Question

Is there an Excel formula that can detect whether two cells in the same column contain similar or partially matching text?

For example:

F3 contains: "regulation BI, how to stop exploitation";
F15 contains: "apples cause natural disasters, regulation BI".

I want a formula that returns TRUE/FALSE when there is a partial match - in this case, both cells contain "regulation BI" - and ideally can also list which cells match.

I have tried formulas like:

=IF(ISNUMBER(SEARCH(F3,F15)),"Yes","No")

But this did not return a partial match.

Any help would be appreciated. Thanks!

And if you don't have a master list of the keywords you're attempting to match, see Excel find partial match in column — devlin carnate
– devlin carnate, Commented Nov 17 at 23:31
I have answered your first question as to the matching words. Your second question makes no sense as the only possible matching cells must be F3 and F15 according to your question. If you mean something else, please post a new question which is clearer. Suggest you first read the HELP pages for information as to How to Ask a Good Question, and How to create a Minimal, Complete, and Verifiable example. — Ron Rosenfeld
– Ron Rosenfeld, Commented Nov 18 at 0:52

Harun24hr · Accepted Answer · 2025-11-18 02:09:22Z

1

For only match, can be used-

=OR(ISNUMBER(SEARCH(TEXTSPLIT(F3,,{" ",","}),F15)))

For listing matching words, can try-

=LET(x,TEXTSPLIT(F3,,{" ",","}),TEXTJOIN(", ",1,FILTER(x,ISNUMBER(SEARCH(x,F15)))))

answered Nov 18 at 2:09

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

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

Comments

Ron Rosenfeld · Accepted Answer · 2025-11-18 00:47:52Z

0

To return the matching values in F3 and F15, split the words into an array and then match word by word:

=LET(
    a, REGEXEXTRACT(F3, "\w+", 1, 1),
    b, REGEXEXTRACT(F15, "\w+", 1, 1),
    c, XLOOKUP(a, b, b, ""),
    TEXTJOIN(", ", TRUE, c)
)

answered Nov 18 at 0:47

Ron Rosenfeld

62.1k7 gold badges35 silver badges71 bronze badges

Collectives™ on Stack Overflow

How to check if two cells in the same column contain matching text in Excel?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related