see the "Unlock Mr. Excel Challenge" banner at the top of the page. That may help your situation. Its a similar problem with some solutions.:

Hye,

If one list has

Public, John Q

and another is

John Public

you may want to consider a formula that will put the names in a similar format. Is this is what is going in, please describe the format the name is currently in, and what format you would like to get it to.

Well thanks for the advise, but the names are companies, and the typical things I have are like this (they are often in French)

'Procter & Gamble' vs. 'Procter and Gamble'

'Unilever Europe' vs. 'Unilever S.A.'

'Banque Degroof' vs. 'Degroof'

So I cannot speak about a format...

Thanks

Joel --

What follows might be of some help.

Lets say that A1:A4 houses the 1st list, which is:

{"Procter and Gamble";

"Unilever S.A.";

"Degroof";

"Excellence"}

and C1:C4 the 2nd list, which is:

{"Procter & Gamble";

"Unilever Europe";

"Banque Degroof";

"Excellence"}

Note 1. The lists need not be in the same sheet or of the same length.

In D1 enter: =IF(LEN(G1),IF(SUMPRODUCT((ISNUMBER(SEARCH(G1,$A$1:$A$4)))+0)>0,INDEX($A$1:$A$4,SUMPRODUCT((ISNUMBER(SEARCH(G1,$A$1:$A$4)))*(ROW($A$1:$A$4))))),F1)

In E1 enter: =IF(LEN(F1),F1,IF(SUMPRODUCT((ISNUMBER(SEARCH(LEFT(C1,SEARCH(" ",C1)-1),$A$1:$A$4)))+0)>0,INDEX($A$1:$A$4,SUMPRODUCT((ISNUMBER(SEARCH(LEFT(C1,SEARCH(" ",C1)-1),$A$1:$A$4)))*(ROW($A$1:$A$4)))),""))

In F1 enter: =IF(COUNTIF($A$1:$A$4,C1),C1,"")

In G1 enter: =IF(LEN(E1),"",RIGHT(C1,LEN(C1)-SEARCH("@",SUBSTITUTE(C1," ","@",LEN(C1)-LEN(SUBSTITUTE(C1," ",""))))))

Select D1:G1 and copy down as far as needed.

I've put the result list expressly next to the second list. What this system of formulas does is: (A) Determine if an item in the 2nd list is also in the 1st list-if so, return that item; (B) If A fails, determine if the first word (delimited by a space) of an item in the 2nd list occurs somewhere in the 1st list-if so, return the item from the 1st list that contains the target word; (C) If B fails, determine if the last word of an item in the 2nd list occurs somewhere in the 1st list-if so, return the item from the 1st list that contains the last target word.

Caveat. If there are more than 1 item that contains the target word, the formula will spawn erroneous results. So becareful.

Note 2. If you like to have a copy of the workbook containing the above, just drop me a line.

Aladin

===========