On Apr 15, 8:13*pm, wrote:
On Apr 15, 7:31*pm, Ron Rosenfeld wrote:



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

Thanks *How can I modify this to give the text exactly 2 columns (or
whatever number of columns) over from the match? *Like how 'vlookup'
works. *Is it possible to modify this to deal with blank cells or will
it get way too complicated?

This formula
{=IF(ISNA(INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c 2:c6,A1)),
0))),"",INDEX(c2:c6,MATCH(TRUE,ISNUMBER(SEARCH(c2: c6,A1)),0)))}
doesn't seem to work in Excel 2010. I am testing this on Excel 2010,
but it will be used on an older version of Excel.