Within a single column I would like to identify whether four possible strings
exists and then list a corresponding number in the formula column.

In the past I have used something like this:

=IF(I2="MILLER",1)+IF(I2="DAVIS",2)+IF(I2="SMIT H",3)+IF(I2="WINTER",4)

And while this works, sort of... it is inelegant at best. Excel acutally
adds the numbers together or something and I end up with corresponding
numbers like, 10, 8, 6, and 4.

Further, I would like to search a long string for the word "DAVIS" and not
have to list the entire beginning of the string.

Try something like this if your list of search names isn't too long:
=SUMPRODUCT(COUNTIF(A1,{"*miller*","*davis*","*smi th*"} )*{1,2,3})

Notes:
1)No error checking necessary since the COUNTIF will return zero if no match.
2)The fomrula won't work if the cell contains multiple searched values.
example: A1: Mr. Miller Davis Smith

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Nicole Seibert" wrote:

Within a single column I would like to identify whether four possible strings
exists and then list a corresponding number in the formula column.

In the past I have used something like this:

=IF(I2="MILLER",1)+IF(I2="DAVIS",2)+IF(I2="SMIT H",3)+IF(I2="WINTER",4)

And while this works, sort of... it is inelegant at best. Excel acutally
adds the numbers together or something and I end up with corresponding
numbers like, 10, 8, 6, and 4.

Further, I would like to search a long string for the word "DAVIS" and not
have to list the entire beginning of the string.

Thank you so much! You have been a great help. I would never have thought
to work with mathmatical formula.


"Nicole Seibert" wrote:

Within a single column I would like to identify whether four possible strings
exists and then list a corresponding number in the formula column.

In the past I have used something like this:

=IF(I2="MILLER",1)+IF(I2="DAVIS",2)+IF(I2="SMIT H",3)+IF(I2="WINTER",4)

And while this works, sort of... it is inelegant at best. Excel acutally
adds the numbers together or something and I end up with corresponding
numbers like, 10, 8, 6, and 4.

Further, I would like to search a long string for the word "DAVIS" and not
have to list the entire beginning of the string.

You're very welcome.
I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP-Pro


"Nicole Seibert" wrote:

Thank you so much! You have been a great help. I would never have thought
to work with mathmatical formula.


"Nicole Seibert" wrote:

Within a single column I would like to identify whether four possible strings
exists and then list a corresponding number in the formula column.

In the past I have used something like this:

=IF(I2="MILLER",1)+IF(I2="DAVIS",2)+IF(I2="SMIT H",3)+IF(I2="WINTER",4)

And while this works, sort of... it is inelegant at best. Excel acutally
adds the numbers together or something and I end up with corresponding
numbers like, 10, 8, 6, and 4.

Further, I would like to search a long string for the word "DAVIS" and not
have to list the entire beginning of the string.