On Mon, 18 Apr 2011 19:40:50 -0400, Ron Rosenfeld wrote:

On Mon, 18 Apr 2011 16:14:16 -0700 (PDT), wrote:


This just gives me the last text in the list instead of not counting
the blank cells. I may look into modifying that UDF posted earlier
however, I don't think it was written to return the text that was x
number of columns over from the match so I don't think I'll be
successful at modifying it. :)

If all you need is to return something that is a few columns over, you can use the Index(Match... method within the UDF, just as you would outside the UDF. You need to add a third argument for the MatchList, but that modification is easy:

=========================
Option Explicit
Function MatchWord(Phrase As String, WordList As Range, MatchList As Range) As String
Dim re As Object, mc As Object
Dim sPat As String
Dim c As Range
If WordList.Rows.Count MatchList.Rows.Count Then
MsgBox ("WordList cannot be longer than Matchlist")
Exit Function
End If
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)
With WorksheetFunction
MatchWord = .Index(MatchList, .Match(mc(0), WordList, 0))
End With
End If

End Function
===================================

And then your formula might be:

=MatchWord(A2,$B$1:$B$12,$D$1:$D$12)

Because of using the Index(Match... construct, WordList cannot be longer than MatchList, or else there will be an error, so we check for that and exit the function with a message if that situation should be present.



An alternative, if you didn't want to worry about MatchList vs WordList size, would be to use Match to find the location of the word in column B; and then use Offset to return the result a few columns over; but that method is not as flexible.