Searching for mulitple strings and assigning identifying number

N

Nicole Seibert

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>="SMITH",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.
 
R

Ron Coderre

Try something like this if your list of search names isn't too long:
=SUMPRODUCT(COUNTIF(A1,{"*miller*","*davis*","*smith*"} )*{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
 
N

Nicole Seibert

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

Ron Coderre

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

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

XL2002, WinXP-Pro
 
Top