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(D5:D5000,{*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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LOOKUP value in a table and return value in first column of same r 2
Search a random array of cells and return a value of "X" 1
how to lookup a value in a list and return multiple corresponding 2
How to look up a value in a list and return multiple correspondingvalues 2
Formula to return a value for a portion of a text string in a cell 2
How to search for a text string in one worksheet and have it return the whole row 6
Searching in another workbook for a string and returning with the contents of the cell next to it. 1

Top