Try this UDF:

Function ColorMe(r As Range) As String
s = Array("BLK", "BLU", "BRN", "GRN", "GRY", "RED", "WHT")
ColorMe = "Sorry Charlie"
For i = LBound(s) To UBound(s)
If InStr(r.Value, s(i)) Then
ColorMe = s(i)
Exit Function
End If
Next
End Function

--
Gary's Student


"Jim J." wrote:

Is there a method to perform the following function?:
I) Using a list of values (In my case, these are text. For the discussion,
lets assume they are colors in 3 place format, like BLK, BLU, BRN, GRN, GRY,
RED, WHT, etc.).
II) Look in a cell, and see if any of those values exist (NOTE: There will
never be more than one of these values within a cell).
III) If any one of these values is found within the cell, state which value
it is.

For example, the statement might look like this:
=IF(FIND({any of the values listed},E2,1),{whatever value it found in
E2},€�Sorry Charlie€�) - where E2 is the €œWithin Text€� location for the FIND
function.

I can accomplish this with nested IF functions, but this limits me to only
seven values.