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.


[TABLE="width: 557"]
<tbody>[TR]
[TD]Opportunity Name
[/TD]
[TD][/TD]
[TD]Abbr
[/TD]
[/TR]
[TR]
[TD]Journals CC Sept. FY17
[/TD]
[TD][/TD]
[TD]alliance
[/TD]
[/TR]
[TR]
[TD]Journals CC 2016 May FY17
[/TD]
[TD][/TD]
[TD]carli
[/TD]
[/TR]
[TR]
[TD]Journals 2016 Current Collection November FY16
[/TD]
[TD][/TD]
[TD]Carolina consortium
[/TD]
[/TR]
[TR]
[TD]Journals Current Collection 2017 Full - August FY17
[/TD]
[TD][/TD]
[TD]crkn
[/TD]
[/TR]
[TR]
[TD]Journals CC 2017 FY17
[/TD]
[TD][/TD]
[TD]ebsco
[/TD]
[/TR]
[TR]
[TD]Journals CC 2017 FY17- OhioLink
[/TD]
[TD][/TD]
[TD]fokal
[/TD]
[/TR]
[TR]
[TD]Journals CC 2017 FY17
[/TD]
[TD][/TD]
[TD]georgia Open Consortium
[/TD]
[/TR]
[TR]
[TD]Journals CC 2017 FY17- OhioLink
[/TD]
[TD][/TD]
[TD]gwla
[/TD]
[/TR]
[TR]
[TD]Journals CC FY17 LOUIS
[/TD]
[TD][/TD]
[TD]louis
[/TD]
[/TR]
[TR]
[TD]Journals CC - Calgary
[/TD]
[TD][/TD]
[TD]lyrasis
[/TD]
[/TR]
[TR]
[TD]Journals CC FY17
[/TD]
[TD][/TD]
[TD]nerl
[/TD]
[/TR]
[TR]
[TD]New Journal Collection FY17
[/TD]
[TD][/TD]
[TD]ohiolink
[/TD]
[/TR]
[TR]
[TD]JCC August FY17
[/TD]
[TD][/TD]
[TD]scelc
[/TD]
[/TR]
[TR]
[TD]JCC August FY17
[/TD]
[TD][/TD]
[TD]tenn share
[/TD]
[/TR]
[TR]
[TD]New Journals Collection FY17 - Full Medicine
[/TD]
[TD][/TD]
[TD]Tennessee system
[/TD]
[/TR]
[TR]
[TD]JCC August FY17
[/TD]
[TD][/TD]
[TD]ut system
[/TD]
[/TR]
[TR]
[TD]JCC October FY17
[/TD]
[TD][/TD]
[TD]waldo
[/TD]
[/TR]
[TR]
[TD]FY17 New Journal Collection
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JCC October FY17
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JCC October FY17
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JCC September FY17
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JCC September FY17
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Opportunity Name[/TD]
[TD="bgcolor: #FCE4D6"]Abbr[/TD]
[TD="bgcolor: #F8CBAD"]In OppName List?[/TD]
[TD="bgcolor: #C6E0B4"]louis, ohiolink[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #E2EFDA"]louis[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #E2EFDA"]ohiolink[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: #E2EFDA"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet38​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=TEXTJOIN(", ",1,C2:C18)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=IF(SUM(--ISNUMBER(SEARCH(B2,$A$2:$A$23)))>0,B2,"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]