Something like vlookup

[ermis1975]

Hello. I have a sheet 1 with different columns. In one of them D I have text. I want to complete the next colum of sheet 1 E with text that found in sheet 2 column B, if I found in column (sheet1 D) a word from sheet 2 column A. Just like vlookup but I want to match only one world and not all text from the cell.

[Aussiebear]

I'm guessing English is not your main language? I think I have it right so here goes.... Basically you are seeking to determine if, for each cell in Column B of Sheet 2, if it matches any part of the text string in any cell in Column D of Sheet 1 then fill in the adjacent cell in Column E. Is this correct?

[ermis1975]

I think right. Just like vlookup, but I want to match the word of cell A (sheet2) to any word of cell D (sheet1) and if it matches then the value of cell B (sheet2) will fill the cell D (sheet1). Is it clear now? Thanks.

[Aussiebear]

No, you have just complicated the issue again. Please note there are significant differences between that which you appear to ask in Post 1 and that in your post #3. How about you attach a workbook with a before and after example so that it assists with the matter.

[ermis1975]

I want to complete the Column VALUE:

Sheet 1


NAME
VALUE (from sheet 2 TEXT 2)


Monday is a day
DAY


Its Monday
DAY


Peter, Paul
NAME


Tom
NAME



Sheet 2





TEXT 1 (find in Sheet1 NAME)
TEXT 2


Monday
DAY


January
MONTH


Peter
NAME


Tom
NAME

[Paul_Hossler]

Maybe




Option Explicit

Function MatchWord(Words As String, Matches As Range) As String
Dim i As Long, n As Long
Dim v As Variant
Dim Words1 As String

Words1 = vbNullString
For i = 1 To Len(Words)
If Mid(Words, i, 1) Like "[A-Za-z0-9 ]" Then
Words1 = Words1 & Mid(Words, i, 1)
End If
Next I

v = Split(Words1, " ")

n = 0
For i = LBound(v) To UBound(v)
On Error Resume Next
n = Application.WorksheetFunction.Match(v(i), Matches.Columns(1), 0)
On Error GoTo 0

If n > 0 Then
MatchWord = Matches.Cells(n, 2).Value
Exit Function
End If
Next I

MatchWord = vbNullString
End Function

[ermis1975]

Thanks, but I need something more. Before and after the word I look for, may exists and other letters or symbols (/|@! etc). So you can't find the exact word eg. /Peter*

[Paul_Hossler]

Sorry, when you said 'word' I thought you meant standalone 'word'. I did not realize that you wanted to match a piece of the text (i.e. a substring)

This looks in the string for the first substring and if it finds one, it returns the type (?) from the table




Option Explicit

Function MatchWord(Words As String, Matches As Range) As String
Dim i As Long
Dim Words1 As String

Words1 = vbNullString
For i = 1 To Len(Words)
If Mid(Words, i, 1) Like "[A-Za-z0-9 ]" Then
Words1 = Words1 & Mid(Words, i, 1)
End If
Next i

With Matches
For i = .Rows(1).Row To .Rows(.Rows.Count).Row
If InStr(1, Words1, .Cells(i, 1).Value, vbTextCompare) > 0 Then
MatchWord = .Cells(i, 2).Value
Exit Function
End If
Next i
End With

MatchWord = vbNullString

End Function

[ermis1975]

Thanks! Its ok now!!