Create a 2 column table like this:

SW01...ABQ
SW03...BTR
SW05...DAL
SW07...DFW

Assume that table is in the range F2:G5

Enter this formula C5:

=LOOKUP(2,1/SEARCH(F$2:F$5,D5),G$2:G$5)

Copy down as needed

--
Biff
Microsoft Excel MVP


"Tommy" wrote in message
...
I am using Excel 2003. I can't seem to figure out a function. I need help
on looking for a certain value in a long string and return a certain value
in
the column before the string value. Here is the formula I've been using.

For example, here is row 5 and their values:

C5 D5
(blank) 2007SW210123456
C6 D6
(blank) 2006SW010123456

If I am looking for text in D5 that have a value of SW21, if so, return
CMO
in C5.
If I am looking for text in D5 that have a value of SW01, then return ABQ
in
C6.

The formula I've been using in column C cells is:
=LOOKUP(D5:D5000,{*SW01*,*SW03*,*SW05*,*SW07*,*SW0 9*,*SW11*,*SW13*,*SW15*,*SW17*,*SW19*,*SW21*,*SW27 *,*SW29*},{"ABQ","BTR","DAL","DFW","HOU","LIT","LB B","OKC","SAT","CMO","COA","SWA"})

Am I doing something wrong?

Thank you