Return value from a table when value is part of a text string

**Shorrocks** · 05-20-2014, 12:22 PM

I have a list of product descriptions in Column B (starting at B3) and each of these product descriptions contains a text string. I've created a table of the text stings I'm searching for in Column F. I would like to create a formula that will search the list in Column B for the text string in the table I've created in Column F and then if found, populate Column C with that text string found in Column F.

I've attached a sample of the workbook I'm working in. I've come up with the array formula {=IF(ISNUMBER(SEARCH($F$3:$F$11, B3)), $F$3:$F$11, "")} but it doesn't work for every row in column B. It only works if the test string I'm searching for is in the first cell of the table in column F.

Can anyone help me out with making this formula work?

**sktneer** · 05-20-2014, 12:33 PM

Try this Array formula in C3.

Please Login or Register  to view this content.

and then drag down.

**AlKey** · 05-20-2014, 12:38 PM

Try this one

=IFERROR(LOOKUP(2^15,SEARCH($F$3:$F$11,B3),$F$3:$F$11),"")

	B	C
2	Product Description	University Name
3	CANCELLED CPA WK04 UNIV OF TENNESSEE REG 01/20/2014	UNIV OF TENNESSEE
4	CANCELLED CPA WK23 UNIV OF TENNESSEE FIN 06/02/2014	UNIV OF TENNESSEE
5	CANCELLED/CHANGED CPA WK40 SULLIVAN UNIV ONLY ARFB-B 07/01/2013	SULLIVAN UNIV
6	CHANGED CPA WK35 UNIV OF MISSOURI FIN 08/26/2013	UNIV OF MISSOURI
7	CPA WK02 AUBURN UNIV ONLY FIN 01/06/2014	AUBURN UNIV
8	CPA WK02 KGSM AUD 01/06/2014	KGSM
9	CPA WK02 KGSM FIN 01/06/2014	KGSM
10	CPA WK02 KGSM REG 01/06/2014	KGSM
11	CPA WK02 LAKELAND AUD 01/06/2014	LAKELAND
12	CPA WK02 LAKELAND FIN 01/06/2014	LAKELAND
13	CPA WK02 LAKELAND REG 01/06/2014	LAKELAND
14	CPA WK02 SULLIVAN UNIV ONLY R 01/06/2014	SULLIVAN UNIV
15	CPA WK02 UNIV OF AL-TUSCALOOSA ONLY AUD 01/06/2014	UNIV OF AL-TUSCALOOSA
16	CPA WK04 KGSM BUS 01/20/2014	KGSM
17	CPA WK06 AUBURN UNIV ONLY AUD 02/03/2014	AUBURN UNIV
18	CPA WK07 UNIV OF MISSOURI FIN 02/10/2014	UNIV OF MISSOURI
19	CPA WK08 LAKELAND BUS 02/17/2014	LAKELAND

**Shorrocks** · 05-20-2014, 12:44 PM

Wow!! Thanks sktneer!! That worked! Can I trouble you to explain the formula - I'm a bit lost and would love to learn how this works?

**Shorrocks** · 05-20-2014, 12:54 PM

Thanks AlKey!! Your solution worked great also! Thanks so much. You and sktneer both came up with different formulas that worked. I'm having a little bit of trouble understanding your formula - can you please explain it to me? I haven't used an Iserror function before and I don't understand the 2^15?

**AlKey** · 05-20-2014, 01:02 PM

Hi Shorrocks and thank you for the feedback!

here is an explanation of 2^15 by Bernie Deitrick

http://answers.microsoft.com/en-us/o...c-68b599b31bf5

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

Return value from a table when value is part of a text string

LinkBack

Thread Tools

Rate This Thread

Display

Return value from a table when value is part of a text string

Re: Return value from a table when value is part of a text string

Re: Return value from a table when value is part of a text string

Re: Return value from a table when value is part of a text string

Re: Return value from a table when value is part of a text string

Re: Return value from a table when value is part of a text string

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Can I remove the first part of a string of text and keep the second part?

[SOLVED] Find partial text string within another text string return original text into cell.

[SOLVED] Excel 2007 : If a cell contains this part text, or this part text, etc.., return this value, or nothing

Mid function to return part of String

function to return part of a string

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions