Need formula(s) to identify match between select words within text strings in 2 seperate cells

[freeb1893]

I'm trying to develop a formula where I can identify a match between text strings in separate cells, where the words inside the text string between the cells are not necessarily in the same order, and there may be other words in the text string that don't match.

There might be cases where the cells have the same name, but there are additional words in one of the two cells.

Here are some examples:

JOHN SMITH -----------------SMITH,JOHN A.
ARNOLD PALMER-------------PALMER JESSE ARNOLD
ALLEN STEVENS--------------STEVENS, JANE & ALLEN

Ideally, I would like a formula which identifies the 2 largest words in Cell A1, and then matches both those 2 largest words in Cell A1 to any of the words in Cell B1.

The words in Cell B1 may be separated by spaces or commas. (If we can only do a formula which looks at words separated only by spaces, I can remove the commas to make the formula work).

Any idea how to put this into a formula?

 

[bosco_yip]

Or try this non-array formula solution

In C2 enter formula and copy down :

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

Also has this problem of "YA QIN ZHANG" and "ZHANG,YA Q" not matched as XOR LX stated

Regards

 

[freeb1893]

This is great! Nice job! Your right the YA ZHANG example I shouldn't have included as one of my anticipated successful outcomes with the formula. This still is a big help.

Now could we try an alternative formula with a different kind of condition. Kind of similar but different. What if we looked at the 1st word in the A1 text string, and the last word in the A1 text string, and identified a match or no match in B1 based on an exact match between the last word in A1's text string to any word in B1's text string, and a partial match between the first 2 characters of the 1st word in A1's text string to the first 2 characters of any word in B1's text string.

Is that doable?

I'm not sure how this is compatible with your other condition that only the two largest "words" be considered. After all, "KIEUNGAN" is not even a "word" within "KIEU".

That aside, try this array formula**:

=IF(COUNT(SEARCH(" "&INDEX(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),LEN(A1))),N(IF(1,MATCH(LARGE(LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),LEN(A1)))),{1,2}),LEN(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),LEN(A1)))),0))))&" "," "&SUBSTITUTE(B1,","," ")&" "))=2,"Match","No Match")

though I don't agree with your expected result for "YA QUIN ZHANG" and "ZHANG,YA Q" (the two largest words in the former do not appear in the latter).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[ndsutherland]

Try

=IF(SUM(--(ISNUMBER(SEARCH(CHOOSE({1;2},RIGHT(A1,LEN(A1)-MAX(IFERROR(SEARCH("|",SUBSTITUTE(A1,CHAR({32;38;44}),"|",{1,2,3,4,5})),0))),LEFT(A1,2)),B1))))=2,"Match","No Match") confirmed with CTRL+SHIFT+ENTER

This allows for spaces, commas, and ampersands

 

[ndsutherland]

Here is another possibility with a comparison. Xor, feel free to provide an explanation of your's.

Sample

 

[freeb1893]

Wow this is fantastic! Thanks so much!

Try

=IF(SUM(--(ISNUMBER(SEARCH(CHOOSE({1;2},RIGHT(A1,LEN(A1)-MAX(IFERROR(SEARCH("|",SUBSTITUTE(A1,CHAR({32;38;44}),"|",{1,2,3,4,5})),0))),LEFT(A1,2)),B1))))=2,"Match","No Match") confirmed with CTRL+SHIFT+ENTER

This allows for spaces, commas, and ampersands