# 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,

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

## Participate now!

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