| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup text in a string with wildcard and return a certain value
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup text in a string with wildcard and return a certain value
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup text in a string with wildcard and return a certain value
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup text in a string with wildcard and return a certain val
That worked! Thanks Biff!!! You're a Genius!
Tommy "T. Valko" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup text in a string with wildcard and return a certain val
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tommy" wrote in message ... That worked! Thanks Biff!!! You're a Genius! Tommy "T. Valko" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return a character at a certain position in a string of text | Excel Worksheet Functions | |||
| Formula to return part of a text string | Excel Discussion (Misc queries) | |||
| Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
| Find $ in a string of text and return numbers | Excel Discussion (Misc queries) | |||
| Return a formula as text string to a cell | Excel Worksheet Functions | |||
Linear Mode
