Word string match formula

[dmehta]

Hey,

I am looking for a formula, that will match text string in two columns, but shall ignore word order.

Example:

Column A Column B
women shoes shoes women

This shall show as match "yes".

Can anybody help?

Thanks!!!

 

[SirJB7]

Hi, dmehta!
Welcome to Chandoo forums!
About your question, I can't think of a way of doing it with formulas using standard functions. If you're willing to use macros then a solution may be building a UDF (user defined function) and then using it in a formula.
Regards!

 

[Mike86]

If you're talking about just looking at two cells, this will work:

=IFERROR(IF(AND(FIND("shoe",A1)>0,FIND("shoe",B1)>0),"yes",""),"no")

 

[azumi]

Hope this works, pls find the file attach

 

[dmehta]

Hey all,

Thank you!

@azumi, thank you the second row should have shown "no" because "Aplle" isn't in there.

But appreciate it!

 

[azumi]

You're welcome, the second row isn't "Apple" but "Pie", that's why come to "Yes"

 

[NARAYANK991]

Hi ,

If each column can contain at most two words separated by a space , try this formula :

=IFERROR(IF(SEARCH(A2, B2 & " " & B2), "Yes"), "No")

Narayan

 

[shrivallabha]

One more approach based on layout posted by azumi. In cell C2 normally enter following formula and copy down:

=IFERROR(FREQUENCY(-ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(" "&B2," ",REPT(" ",99)),ROW($A$1:INDEX(A:A,LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1))*99,99)),A2)),{-1})/(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1),0)

Format cell as %age which will indicate %age word match with the other cell. Current formula will tell %age match of col B with col A. You just need to reverse the logic if you want to compare opposite.