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.


    Regards