hi,

Am Mon, 18 Mar 2013 15:37:58 +0000 schrieb otteraaron:

Column A is full of product descriptions:

Black Otterbox Case for iPhone
Blue Speck case for iphone
Pink Otterbox case for GS3
White Case Mate case for iphone 5
Blue Otterbox case for iphone 4s

Column B are the "brands" i'm searching for:
OtterBox
Case Mate
Speck

if you only have 3 brands you can use:
=IF(ISNUMBER(FIND("Otter",A1)),$B$1,IF(ISNUMBER(FI ND("Case",A1)),$B$2,IF(ISNUMBER(FIND("Speck",A1)), $B$3,"")))

But if you have more then do it with a UDF:

Function myString(rngC As Range) As String
Dim LRow As Long
Dim c As Range

LRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Range("B1:B" & LRow)
If InStr(LCase(rngC), LCase(c)) 0 Then
myString = c
Exit For
Else
myString = ""
End If
Next
End Function

Then you can call it in C1 with =myString(A1) and copy down.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2