That's much more clear.
If you don't mind the possibility of words contained within other words; in other words:
A B C
blueberry apple berry
berry
Then you can use one of these:
WordList is a contiguous array (no blanks) of your words in column B.
These formulas must be **array-entered**:
For Excel 2007 or later:
=IFERROR(INDEX(WordList,MATCH(TRUE,ISNUMBER(SEARCH(WordList,A1)),0)),"")
For earlier versions, which do not have the IFERROR function:
=IF(ISNA(INDEX(WordList,MATCH(TRUE,
ISNUMBER(SEARCH(WordList,A1)),0))),"",
INDEX(WordList,MATCH(TRUE,
ISNUMBER(SEARCH(WordList,A1)),0)))
----------------------------------------
To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
---------------------------------------------------
If you do not want "blue" to match "blueberry", then the easiest solutionwill be with a VBA User Defined Function. If your words in the column Acells might not start/end with a letter/digit/underscore, the pattern in the UDF may need some modification.
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=MatchWord(A1, WordList)
in some cell.
======================================
Option Explicit
Function MatchWord(Phrase As String, WordList As Range) As String
Dim re As Object, mc As Object
Dim sPat As String
Dim c As Range
Set re = CreateObject("vbscript.regexp")
sPat = "\b("
For Each c In WordList
If Len(c.Text) > 0 Then sPat = sPat & c.Text & "|"
Next c
sPat = Left(sPat, Len(sPat) - 1) & ")\b"
With re
.Global = True
.Pattern = sPat
.ignorecase = True
End With
If re.test(Phrase) Then
Set mc = re.Execute(Phrase)
MatchWord = mc(0)
End If
End Function
=============================
