My face is red. I had a blank in front of all the name entries. When I
removed it all these solutions worked.
Jim Berglund

"Ron Coderre" wrote in message
...
It's a bit difficult to guess the problem without knowing the names that
are being evaluated.

So.....Reverse engineering from your posted formula....

M1: (blank)
M2: Dr
M3: Miss
M4: Mr
M5: Mrs
M6: Ms

Try this variation
L1: =T(INDEX($M$1:$M$6,MAX(INDEX(COUNTIF(C2,$M$2:$M$6& {". *","
*"})*{1;2;3;4;5},0))+1))
Note_1: There's a space before the asterisk)
Note_2: That formula matches titles with, or without, a period.
eg Mr. vs Mr
Note_3: BUT it returns the no-period version from the list

Examples:
If C1: Mr Bigshot_____Then L1 returns: Mr
If C1: Mr. Bigshot_____Then L1 returns: Mr


If you want to match the cell contents version of the title:
L1: =IF(MAX(INDEX(COUNTIF(C1,$M$2:$M$6&{". *","
*"})*{1;2;3;4;5},0)),LEFT(C1,SEARCH(" ",C1)),"")
Note_4: There is a space before each asterisk

Examples:
If C1: mr Bigshot_____Then L1 returns: mr
If C1: mr. Bigshot_____Then L1 returns: mr.

Does that help?
If you still have issues, post some sample names.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Jim Berglund" wrote in message
...
Thanks, Ron. I tried it but came up with a column of blanks.

What I did was to create the indesx in column M and enter the formula it
into Cell L1 of my spreadsheet as
=T(INDEX($M$1:$M$6,MAX(INDEX(COUNTIF(C1,$M$2:$M$6& "*")*{1;2;3;4;5},0))+1))

The names are located in column C.

Is there something wrong with my porcedure?

Jim Berglund

"Ron Coderre" wrote in message
...
Try something like this:

Using this list (in alphabetical order):
E1: (blank)
E2: Dr
E3: Miss
E4: Mr
E5: Mrs
E6: Ms

and a name, with or without title, in cell A1

This formula returns the Title:
C1: =T(INDEX($E$1:$E$6,MAX(INDEX(COUNTIF(A1,$E$2:$E$6& "
*")*{1;2;3;4;5},0))+1))

Examples:
A1: Dr Dave
A2: Drew Carey
A3: Miss Smith
A4: Mississippi Mary

These title values are returned:
C1: Dr
C2: (blank)
C3: Miss
C4: (blank)

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Jim Berglund" wrote in message
...
I'm trying to create a mailing list and have a list containing Mr, Ms,
Miss, Mrs, Dr, etc. Some names have no salutations. I want to use a
macro that looks for the first name if and after one of these surnames
occurs.

I tried multiple IF's, as below?

=IF(LEFT(A31,3)="Mrs","Mrs",IF(LEFT(A31,4)="Miss", "Miss",IF(LEFT(A31,2)=OR("Mr","Ms","Dr"),LEFT(A31, 2),"")))

The frst part works for Mrs * Miss, but the OR section gives me a
#Value error.

I there a better way?

Jim Berglund