|
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com