Should the first value be merely D5 rather than D5:5000? Then as you copy
down the column, it auto-increases..

"Tommy" wrote:

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