# Lookup text in a string with wildcard and return a certain value

T

#### Tommy

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(D55000,{*SW01*,*SW03*,*SW05*,*SW07*,*SW09*,*SW11*,*SW13*,*SW15*,*SW17*,*SW19*,*SW21*,*SW27*,*SW29*},{"ABQ","BTR","DAL","DFW","HOU","LIT","LBB","OKC","SAT","CMO","COA","SWA"})

Am I doing something wrong?

Thank you

S

#### Sean Timmons

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

T

#### T. Valko

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

T

#### Tommy

That worked! Thanks Biff!!! You're a Genius!

Tommy