Try this:

With your list of categories in the range J1:J4

Enter this formula in B1:

=LOOKUP(2,1/(ISNUMBER(SEARCH(J$1:J$4,A1))),J$1:J$4)

Copy down as needed.

Note that if a cell contains 2 or more categories: Other - Mechanical
Estimator, the formula will return the *last* category that matches. In this
case both Other and Mechanical are matches but Other appears in the category
list after Mechanical so the result of the formula is Other.


--
Biff
Microsoft Excel MVP


"James" wrote in message
...
Hi all,

I have a list of categories to indicate employee disciplines, i.e
Management
Electrical
Mechanical
Other
and so on.

I have other cells within my spreadsheet that have text that includes the
categories above. For example,

Electrical Engineer
Senior Electrical Engineer
Junior Electrical Engineer

I would like to search these text strings using the categories above to
return only the category. Therefore, if i had A1 = Electrical Engineer,
B1
needs to be a formula that gives the result of Electrical. If I then
changed
A1 to Mechanical Engineer, B1 needs to automatically change to Mechanical.

Any suggestions

--
J