Solved: VLOOKUP or INDEX/MATCH ?

[TrippyTom]

Hi everyone,

I've run into something I can't seem to wrap my brain around so I'm coming to you for your knowledge and experience.

My Problem:
file1 = "MASTER.xls". It has 362 rows with 2 columns. COL A = various text I want to look for, COL B = "Threat Level" (either LOW or HIGH)

file2 = an auto-generated report by our system.

What I want to do:
I would like to check the first column in that report for ANY occurrence of the words in my master list. I thought a wildcard in a VLOOKUP formula would work, but I don't quite have the syntax because it's only correctly reporting entire cells matching (not parts of the cell). Or maybe I'm going at this the wrong way.

Since this is an auto-generated report run weekly, I would prefer to put this in a macro. Here's what I have so far.

[vba]
Sub test()
Application.ScreenUpdating = False
'On Error Resume Next

Dim lastRow As Long
Dim i As Integer
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant

'Copy format of column headers to new column named "Threat Level"
Range("B5").Value = "Threat Level"
Range("A5").Copy
Range("B5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'find last row
lastRow = Range("A65536").End(xlUp).Row

'Set alignment in results columns to Top and Left
With Range("B:B")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With

'--VLOOKUP PASS--
Set rng = Workbooks("MASTER.xls").Sheets(1).Range("keywords")
col = 2

For i = 6 To lastRow
Set lookFor = Range("A" & i)
'I used wildcards before and after the lookup value from the MASTER list here.
Range("B" & i).Formula = Application.VLookup("*" & lookFor.Value & "*", rng, col, 0)
Next i

Range("A1").Select

Application.ScreenUpdating = True
End Sub
[/vba]

Could anyone steer me toward the light? This is not homework. My boss "volunteered" me to figure this out for someone.

[TrippyTom]

I think I just realized my problem:
[vba]
Range("B" & i).Formula = Application.VLookup("*" & lookFor.Value & "*", rng, col, 0)
[/vba]

The lookFor.Value is taking the entire cell and comparing that to my Master List. Do I need a nested VLOOKUP inside there to search within the cell?

[TrippyTom]

Thanks to this link I think I figured it out:
http://www.ozgrid.com/forum/showthread.php?t=37137

I parsed it out (space = delimiter) into an array and did the search on the array. It SEEMS TO work, but I'll need to do further testing to be sure.

I thought I would post the apparent solution in case anyone else had the same question.

Thanks
-Tom

[vba]
Sub test()
Application.ScreenUpdating = False
'On Error Resume Next

Dim lastRow As Long
Dim i As Integer
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant
Dim avarSplit As Variant
Dim intIndex As Integer

'Copy format of column headers to new column named "Threat Level"
Range("B5").Value = "Threat Level"
Range("A5").Copy
Range("B5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'find last row
lastRow = Range("A65536").End(xlUp).Row

'Set alignment in results columns to Top and Left
With Range("B:B")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With

'--VLOOKUP PASS--
Set rng = Workbooks("MASTER.xls").Sheets(1).Range("keywords")
col = 2

For i = 6 To lastRow
avarSplit = Split(Range("A" & i).Value, " ")
For intIndex = LBound(avarSplit) To UBound(avarSplit)
'I used wildcards before and after the lookup value from the MASTER list here.
Range("B" & i).Formula = Application.VLookup("*" & avarSplit(intIndex) & "*", rng, col, 0)
Next
Next i

Range("A1").Select

Application.ScreenUpdating = True
End Sub
[/vba]