Partial Match formula based on words in 2 cells

  • Hello :),

    I am trying to do partial match based on words in both columns.

    I have two data in Col A and Col B and the formula goes in Cell C2 and down till last row.

    So for example, I have these in A2 and B2.

    A2: The White Lion King ---------B2: The Lion King -----------------------------C2: Formula should return Match because 3 words from B2 match A2

    A3: Brown Horse -------------------B3: Lazy Fox ------------------------------------C3: Formula should return No Match because words don't match

    A4: The Great Scientist -----------B4: The Great Newton Scientist---------C4: Formula should return Match because 3 words match

    I am trying to do it with Search formula. Tried find as well but to no avail. What formula can we use here for partial match.

    Many thanks

  • Try,

    In C2, formula copied down :

    =IF((MAX(MMULT(--ISNUMBER(SEARCH(MID(A2,COLUMN(INDIRECT("C1:C"&LEN(A2),)),1),B2)),ROW(INDIRECT("1:"&LEN(A2)))^0))-LEN(A2)+LEN(SUBSTITUTE(A2," ","")))/LEN(A2)>0.5,"Match","No Match")

  • Dear Bosco,

    Thank you very much for your reply.

    It shows Match and No Match but for some strings, it still shows Match when there is none. For example,

    A2: Feelings: A Baby and Blue Book-------------------------------------------------------B2: Payback (Fearless, No. 6)

    A3: Goldberg, Lee: The Death Merchant (Diagnosis Murder # 2)---------------B3: Sun Hawk

    A4: Treasure Island and Other Cartoon--------------------------------------------------B4: I Who Have Never Known Men

    A5: Harry Potter and the Sorcerers Stone-----------------------------------------------B5: The Changing of the Guard (Star Wars: Jedi Quest)

    Thank you very much for your help.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!