View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Match similar names

On Tue, 26 Jan 2010 03:18:02 -0800, Sam wrote:

A1 B1
Frank Mark Allen Frank Mark Allen Frank Mark

I want to compare two cells. A1 contain full name , B1 contain short name .
searching for formula to do a minimum match.


I made the following assumptions:

The short name will have two and only two words

If the two names in the short name exist in the same order in the long name
(but not necessarily adjacent), then there is a match.

=IF(ISNUMBER(FIND(LEFT(B1,FIND(" ",B1)-1),A1)+
FIND(TRIM(MID(B1,FIND(" ",B1),99)),A1,FIND(
LEFT(B1,FIND(" ",B1)-1),A1))),"Match","No Match")

--ron