Check whether certain words is contained within a string from a list

[HotNumbers]

Hello,
I need help. Need to Check cells with string of words separated by semicolon. The cell is checked against a master list of words on a another tab.

Example

Cell A1

APPLE;PAIR;TREE;GREEN;ORANGE;HAPPY

DATA TABLE

APPLE
ORANGE
PAIR


Expected Results based on the master table match
Cell B1
APPLE;PAIR;ORANGE

 

[mse330]

Hey HotNumbers,

This is a UDF (User Defined Function) which should do what you need. It has 3 arguments first being the text you need to search, 2nd is the range of words you are looking from, and the 3rd argument is optional (can be ignored) which is case sensitive - default is it's not case sensitive, if you need it case sensitive either put 'True' or 1 for the 3rd argument of the function. You can see how it works in the below example

Option Base 1
Function FindMatches(Txt As String, Rg As Range, Optional CaseSensative As Boolean) As String
Dim Dict As Object, Ar1() As Variant, Ar2() As Variant, Ar3() As String, Cnt As Long
Set Dict = CreateObject("Scripting.Dictionary")
If CaseSensative = False Then Dict.CompareMode = vbTextCompare
Ar1 = Rg
For i = LBound(Ar1) To UBound(Ar1)
    If Not Dict.exists(Ar1(i, 1)) Then Dict.Add Ar1(i, 1), Nothing
Next i
Ar3 = Split(Txt, ";")
For i = LBound(Ar3) To UBound(Ar3)
    If Dict.exists(Ar3(i)) Then
        Cnt = Cnt + 1
        ReDim Preserve Ar2(Cnt)
        Ar2(Cnt) = Ar3(i)
    End If
Next i
FindMatches = Join(Ar2, ";")
End Function

Book1
	A	B	C	D
1	Original Text	UDF Output		LookUp Data
2	APPLE;PAIR;TREE;GREEN;ORANGE;HAPPY	APPLE;PAIR;ORANGE		APPLE
3	PLANE;HOUSE;BOAT;car	PLANE		ORANGE
4				PAIR
5				CAR
6				Boat
7				PLANE
Sheet1

Cell Formulas
Range	Formula
B2	=FindMatches(A2,$D$2:$D$7)