Search for instances of Data from Table Array within cells in a column

[jziboukh]

I have tried various types of searches to get the help I need, but it either doesn't exist or, more likely, I am not using the correct language to search. I'm not sure this is something that can be done with a formula. I'm not opposed to VBA, but prefer a formula. Essentially I feel like I need a search with wildcards, but I don't know how to do that using a table array. I've done it as results in individual cells [=IF(ISNUMBER(SEARCH("*alliance*",$C3)),"TRUE","FALSE")], but there are too many possibilities and the spreadsheet is getting too large.. I don't want it to be case sensitive. I can probably weed out any false matches pretty quickly. There are nearly 2000 rows to search and the list of abbreviations may grow to 200+. This is just a sample.

A formula to search the Opportunity Name column to see if there are any instances of the data in the Abbr table (preferably as a separate "word" and not as part of a word in a given cell). If so, return the appropriate abbreviation name. If not, leave the cell blank.

Opportunity Name		Abbr
Journals CC Sept. FY17		alliance
Journals CC 2016 May FY17		carli
Journals 2016 Current Collection November FY16		Carolina consortium
Journals Current Collection 2017 Full - August FY17		crkn
Journals CC 2017 FY17		ebsco
Journals CC 2017 FY17- OhioLink		fokal
Journals CC 2017 FY17		georgia Open Consortium
Journals CC 2017 FY17- OhioLink		gwla
Journals CC FY17 LOUIS		louis
Journals CC - Calgary		lyrasis
Journals CC FY17		nerl
New Journal Collection FY17		ohiolink
JCC August FY17		scelc
JCC August FY17		tenn share
New Journals Collection FY17 - Full Medicine		Tennessee system
JCC August FY17		ut system
JCC October FY17		waldo
FY17 New Journal Collection
JCC October FY17
JCC October FY17
JCC September FY17
JCC September FY17

<tbody>
</tbody>

Thank you in advance for your help!

 

[DRSteele]

Welcome to the forum.

I've achieved most of your objective by using a helper column; copy formula C2 down as necessary. I also added a single-cell formula (D1) using the new function TEXTJOIN to list the results of matches between the lists.

	A	B	C	D
1	Opportunity Name	Abbr	In OppName List?	louis, ohiolink
2	Journals CC Sept. FY17	alliance
3	Journals CC 2016 May FY17	carli
4	Journals 2016 Current Collection November FY16	Carolina consortium
5	Journals Current Collection 2017 Full - August FY17	crkn
6	Journals CC 2017 FY17	ebsco
7	Journals CC 2017 FY17- OhioLink	fokal
8	Journals CC 2017 FY17	georgia Open Consortium
9	Journals CC 2017 FY17- OhioLink	gwla
10	Journals CC FY17 LOUIS	louis	louis
11	Journals CC - Calgary	lyrasis
12	Journals CC FY17	nerl
13	New Journal Collection FY17	ohiolink	ohiolink
14	JCC August FY17	scelc
15	JCC August FY17	tenn share
16	New Journals Collection FY17 - Full Medicine	Tennessee system
17	JCC August FY17	ut system
18	JCC October FY17	waldo
19	FY17 New Journal Collection
20	JCC October FY17
21	JCC October FY17
22	JCC September FY17
23	JCC September FY17

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet38​

Worksheet Formulas Cell Formula D1 =TEXTJOIN(", ",1,C2:C18) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C2 {=IF(SUM(--ISNUMBER(SEARCH(B2,$A$2:$A$23)))>0,B2,"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>