ExcelBanter - View Single Post

Ron Rosenfeld[_2_]

On Fri, 15 Apr 2011 16:55:21 -0700 (PDT), wrote:

I just want to compare the phrase in one cell (A1 for example) to a
range of cells in column B (for example B1:B10) and wherever the match
is found return the text in the B cell. With the list I am using
there shouldn't be any duplicates.

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 solution will be with a VBA User Defined Function. If your words in the column A cells 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
=============================