Searching for mulitple strings and assigning identifying number
 

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.

Searching for mulitple strings and assigning identifying number
 

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.

Searching for mulitple strings and assigning identifying number
 

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.

Searching for mulitple strings and assigning identifying numbe
 

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.