Share via

Excel partial match when comparing two cells

Anonymous
2020-02-11T22:01:25+00:00

I'd like to figure out how to determine if any part of cell A1 is contained in any part of cell A2.

For instance, if cell A1 has Roger Johnson and cell A2 has Johnson Motors I want to it come back with a partial match because Johnson appears in both cells. 

Note: I do not have and cannot get Kutools.

Anybody have any suggestions?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 101.9K Reputation points Volunteer Moderator
2020-02-15T09:05:14+00:00

Hi,

In cell C2, enter this formula and copy down

=ISNUMBER(LOOKUP(9E+300,SEARCH(TRIM(MID(SUBSTITUTE(A2&REPT(" ^^^",LEN(A2))," ",REPT(" ",999)),ROW(INDIRECT("1:"&LEN(A2)))*999-998,999)),B2)))

Hope this helps.

Was this answer helpful?

4 people found this answer helpful.
0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-02-12T04:59:14+00:00

    Hi There ...

    There can be multiple approaches based on your exact requirement by either excel front end formula or VBA code.

    a) How many words are you expecting in A1 ? Will be always 2 (XXX YYY) ?

    b) Delimiter will always be space between words ?

    Once this input is given exact approach can be figured out.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-02-11T23:00:56+00:00

    TRUE if there is a partial match and FALSE if no match.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-02-11T22:19:59+00:00

    Power Query which is built into Excel 2016 (or free download for Excel 2013 and 2010) now includes a Fuzzy Match feature.  That might be a good option.

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2020-02-11T22:19:35+00:00

    What should the formula return?

    • TRUE if there is a partial match, FALSE if not?
    • Or the text the two cells have in common ("Johnson" in your example)?
    • Or something else (if so, what)?

    Was this answer helpful?

    0 comments