How to Extract text from string using a lookup table

Hi,

See if this guides you in the right direction...

http://www.youtube.com/user/ExcelIsFun#p/search/0/j0aXSaGfnek

Good luck.

Ak

HotNumbers said:

How do I extract text from a string using a lookup Table to identify which string to bring back. example

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Data Source							Lookup Table
2
3	STRING	RETURN						TABLE
4	24 hour internet access							Home
5	24 x 7 Communications Support							Travel
6	3G Account							Fax
7	3G account for IPad							iPad
8	3G Monthly Subscription							Kindle
9	3G network							Office
10	3G Wireless Network access							Monthly
11	5 days of internet access
12	80% of Home internet bill			If a string in clumns A has any of the words in column I I would like for columb B
13	80% of Home internet invoice			to get pupulated by that Text in column I
14	80% of home internet invoice - January 2010
15	80% of home internet invoice - July 2010
16	80% of Home Internet Invoice - March 2010
17	80% of Internet Bill at home
18	A T& T Charges - FAX Line
19
20
21
22
Sheet1

Excel 2007

Click to expand...

It looks like your word table is in column H?

Try this...

=IFERROR(LOOKUP(1E100,SEARCH(H$4:H$10,A4),H$4:H$10),"")

Note that if a string contains more than one keyword the formula will return the word that is closest to the bottom of the list.

Hi,

Well Biff has beaten me to it, but here's my version any how...

=LOOKUP(2^15,SEARCH({"Home";"Travel";"Fax";"iPad";"Kindle";"Office";"Monthly"},A4),$H$4:$H$10)

This will return #N/A if there is no match.
I got this part of the formula...
{"Home";"Travel";"Fax";"iPad";"Kindle";"Office";"Monthly"}
by highlighting $H$4:$H$10 and pressing F9, it's just to show what you are searching for.

Ak

AKASHWANI you are a megician... this is what i needed... Thanks a Million.

T.Valko.... YOUR posting is also correct and the simplest. CAN YOU please explain to me what 1E+100 means in the formula

HotNumbers said:

T.Valko.... YOUR posting is also correct and the simplest. CAN YOU please explain to me what 1E+100 means in the formula

Click to expand...

The result of the SEARCH function will be either an error or a number from 1 to 32,767 (the maximum number of characters that a cell can hold).

For the formula to work correctly we need to find the last number that SEARCH will return. In order to do that we have to use a lookup value that is larger than 32,767.

In the formula I use the lookup value of 1E100 which is scientific notation ("shorthand") for a very large number, 1 followed by 100 zeros. This number is certainly larger than 32,767 so the formula works as expected.

I like to use 1E100 just because it's nice and compact and easy to remember. Technically, all you need to use as the lookup value is ANY number greater than 32,767. So, you could use 32,768 but 1E100 is more compact and is easy to remember.

Thanks for the explination...this was the magic bullet that has helped my file.

HotNumbers said:

Thanks for the explination...this was the magic bullet that has helped my file.

Click to expand...

You're welcome. Thanks for the feedback!