VBA: Multiple Keyword vlookup

Question

I have a number of narrative descriptions that I need to categorize automatically in Excel:

Description                       Category
I updated the o.s.  
I installed the o.s.    
I cleaned valve a   
I cleaned valve b   
I installed valve a 
Today the o.s. was updated

I have another worksheet with keywords and the category the keywords are associated with:

Keyword 1   Keyword 2   Keyword 3   Category
cleaned      valve         a           A
installed    valve         a           B
updated       os                       C
installed     os                       D

My code so far can only search one keyword at a time and therefore will report incorrect answers because some keywords are used in multiple narratives:

Public Function Test21(nar As Range, ky As Range) As String

Dim sTmp As String, vWrd As Variant, vWrds As Variant

'Splits Fsr Narrative into individual words so it can be searched for keywords'
vWrds = Split(nar)

For Each vWrd In vWrds

    If Not IsError(Application.VLookup(vWrd, ky, 3, False)) Then
    sTmp = Application.VLookup(vWrd, ky, 3, False)
    Exit For
    End If
Next vWrd

Test21 = sTmp

End Function

I've seen algorithms like this but I feel that my goal could be simpler to accomplish as all narratives are relatively simple.

Thanks for reading!

more specifically, based on your example, you'll need to delimit `nar` by the ` (space)` ... `vWrds = Split(nar," ")` — Scott Holtzman, Jun 23 '16 at 18:51

score 0 · Answer 1 · answered Jun 23 '16 at 19:36

You can match multiple columns with a VLOOKUP by creating a "match column" that concatenates the multiple values together, then searching that column for a match.

So if you use this formula in column A:

=B1 & "|" & C1 & "|" & D1

You can then VLOOKUP against that match column:

=VLOOKUP("blah|bleh|ugh", 'Sheet2!A1:E100', 5, FALSE)

Which will match the one row that has "blah" in column B, "bleh" in column C, and "ugh" in column D, and return the value in column E.

For your data though, I think you might also want to have a step to clean up your input before trying to match a set of keywords. The method I described above works best if the keywords are in a particular order, and where you won't have any non-keywords cluttering up things. (It also works excellently for vlookups where you want to match multiple pieces of data, ie. first name, middle name, and last name in different columns)

Otherwise you could end up needing an incredibly huge number of rows in your category table to cover every possible combination and permutation of your keywords and the other random words they might be accompanied by.

score 0 · Accepted Answer · answered Jun 23 '16 at 19:52

0

This is what I was looking for:

Public Function Test22(nar As Range, key As Range, cat As Range) As String

For r = 1 To key.Height
If InStr(nar, key(r, 1)) And InStr(nar, key(r, 2)) Then
Test22 = cat(r)
Exit For
End If
Next r

End Function

answered Jun 23 '16 at 19:52

xbraatz

1
1

VBA: Multiple Keyword vlookup

2 Answers2