I have a column with keywords and another column with product names, I want to match what product names do to what keywords

Column A has Product Names. Column E has Keywords in sets of 1, 2 and 3 words. Column F has a Catalogue Name.

I want Column B to be filled with the Catalogue Name every time a Product Name Fits a Keyword.

I have over 100.000 rows, how would you do it to make it fast? Use of Arrays? Dictionaries?

I tried doing nested cycles, but it is highly inefficient and slow.

What this code does is split the first set of keywords in to separate words.
Example: Red Mitsubishi into:
aWord(0): Red
aWord(1): Mitsubishi

Then it separates the first product name in to separate words.
Example: Red Cool Mitsubishi
pWord(0): Red
pWord(1): Cool
pWord(2): Mitsubishi

Then if each word of the first keyword finds an equal word in the words of the first product, then we found a match.

It will do that with each product name. Once it finished it will pass to the second set of keywords and do the same with each and every product name again, that until its finished with all the keywords.

Sub MatchinKeyWords()
    Dim keyWordsArr, productNameArr
    Dim i As Long, x As Long, N As Long
    Dim keyword, productWord
    Dim matchFound As Boolean
    Dim matchedRows() As Boolean

    ' Load arrays from ranges
    productNameArr = Range("A1").CurrentRegion.Value
    keyWordsArr = Range("D1").CurrentRegion.Value

    ' Initialize the array to track matched rows
    ReDim matchedRows(LBound(productNameArr, 1) To UBound(productNameArr, 1))
    For i = LBound(matchedRows) To UBound(matchedRows)
        matchedRows(i) = False
    Next i

    ' Iterate over keyWordsArr
    For i = LBound(keyWordsArr, 1) + 1 To UBound(keyWordsArr, 1)
        keyword = Split(keyWordsArr(i, 1))

        ' Check against each productNameArr
        For x = LBound(productNameArr, 1) + 1 To UBound(productNameArr, 1)
            ' Skip if this productNameArr row was already matched
            If matchedRows(x) Then GoTo NextProduct

            productWord = Split(productNameArr(x, 1))
            matchFound = True

            ' Check each keyword
            For N = LBound(keyword) To UBound(keyword)
                If IsError(Application.Match(keyword(N), productWord, 0)) Then
                    matchFound = False
                    Exit For
                End If
            Next N

            ' If match is found
            If matchFound Then
                productNameArr(x, 2) = keyWordsArr(i, 2) ' Fill in the match info
                matchedRows(x) = True ' Mark this row as matched
            End If

NextProduct:
        Next x
    Next i

    ' Print updated productNameArr to the worksheet
    Range("A1").Resize(UBound(productNameArr, 1), UBound(productNameArr, 2)).Value = productNameArr
End Sub

Solution

To save time, organize keywords in a Dictionary object.
Note: the keyword table is slightly different from yours.

For example,

The first step is to organize keywords. For any product names that include "CAR", it should only validate with the first two keywords.

objDic("CAR") is a nested dictionary object with two items.

[key: MITSUBISHI RED CAR value: MITSUBISHI]
[key: HYUNDAI UGLY CAR value: HYUNDAI]

Key Words	Catalogue
MITSUBISHI RED CAR	MITSUBISHI
HYUNDAI UGLY CAR	HYUNDAI
FAST	SPEED

Option Explicit

Sub Demo()
    Dim objDic As Object, rngData As Range
    Dim i As Long, j As Long, sKey As String
    Dim arrData, arrKW, vProd, vWord, vKey, aKey
    Dim bMatch As Boolean
    Set objDic = CreateObject("scripting.dictionary")
    ' Load key words into Dict
    arrKW = Range("D1").CurrentRegion
    For i = LBound(arrKW) + 1 To UBound(arrKW)
        sKey = arrKW(i, 1)
        For Each vKey In Split(arrKW(i, 1))
            If objDic.exists(vKey) Then
                objDic(vKey)(sKey) = arrKW(i, 2)
            Else
                Set objDic(vKey) = CreateObject("scripting.dictionary")
                objDic(vKey)(sKey) = arrKW(i, 2)
            End If
        Next
    Next i
    ' Load product name
    Set rngData = Range("A1").CurrentRegion
    arrData = rngData.Value
    ' Loop through products
    For i = LBound(arrData) + 1 To UBound(arrData)
        sKey = arrData(i, 1)
        ' Check each words in product name
        For Each vProd In Split(arrData(i, 1))
            If objDic.exists(vProd) Then
                ' Matching related key words
                For Each vKey In objDic(vProd).Keys
                    bMatch = True
                    For Each vWord In Split(vKey)
                        If InStr(1, sKey, vWord, vbTextCompare) = 0 Then
                            bMatch = False
                            Exit For
                        End If
                    Next
                    ' Fully matching
                    If bMatch Then
                        arrData(i, 2) = objDic(vProd)(vKey)
                        Exit For
                    End If
                Next
            End If
        Next
    Next i
    rngData.Value = arrData
End Sub

btw, If you apply the same logic as in the previous post to organize keywords with more words as dictionary keys, it can save more time for a large dataset.

Count 3 most repeated words in product names with VBA