find data containing key words

[NKUNZI]

Is there a way of extracting data from another sheet to be placed in the current sheet based on certain key words.
in short I have two sets of data that indicate the same thing but does not match exactly, and I would like to pull in the data based on key words.
to give an example:-
the list that i want to copy in the new sheet looks like this

Abacavir; 60mg; Tablet, dispersible; 56 Tablets the list that I want to use to pull this in is Abacavir 60mg Tablets; 56 [po] the thinking was that if I extract up to Abacavir 60mg, then use that as the key I could then find Abacavir; 60mg; Tablet, dispersible; 56 Tablets in the other sheet, copy and paste it next to the data in the current sheet Vlookup, or index match doesn't cut it in this instance The lookup list contains around 500 entries, and the list being used for key words varies from around 10 to 90 Hope I have explained clearly what I am looking for in text format it translates as such:- find in sheet 1 the text that matches best with the selected text found in sheet 2 once found copy the text from sheet 1 into the column next to the text used for the search from sheet 2 Ultimately this would be used in index(match) to extract data into the adjacent columns Thank you

 

[NdNoviceHlp]

Hmmm.... In my testing it did what I thought it was supposed to do with some data that I created from your example. Trial changing these 3 lines of code. Dave

'split product at " "
Splitter = Split(CStr(Sheets("Product Data").Range("T" & Cnt)), " ")
'split MSDS at ";"
Splitter2 = Split(CStr(Sheets("MMDS Sheet").Cells(Cnt2, 3)), ";")
'compare drug
If InStr(CStr(Sheets("MMDS Sheet").Cells(Cnt2, 3)), Splitter(0)) Then

ps. Are there any blank cells in the data?

 

[NdNoviceHlp]

Blank cells in the Product Data cause an error. So trial #4...

Sub test()
Dim LastRowT As Integer, LastRowC As Integer, Cnt As Integer, Cnt2 As Integer
Dim Splitter As Variant, Splitter2 As Variant
With Sheets("Product Data")
LastRowT = .Range("T" & .Rows.Count).End(xlUp).Row
End With
With Sheets("MMDS Sheet")
LastRowC = .Range("C" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To LastRowT
For Cnt2 = 2 To LastRowC '2300
If Sheets("Product Data").Range("T" & Cnt) <> vbNullString Then
'split product at " "
Splitter = Split(CStr(Sheets("Product Data").Range("T" & Cnt)), " ")
'split MSDS at ";"
Splitter2 = Split(CStr(Sheets("MMDS Sheet").Cells(Cnt2, 3)), ";")
'compare drug
If InStr(CStr(Sheets("MMDS Sheet").Cells(Cnt2, 3)), Splitter(0)) Then
'compare dose
If Splitter(1) = Right(Splitter2(1), Len(Splitter2(1)) - 1) Then
'compare quantity and distribution
If Splitter(3) & " " & Splitter(4) = Right(Left(Splitter2(3), Len(Splitter2(3)) - 1), _
                                    Len(Left(Splitter2(3), Len(Splitter2(3)) - 1)) - 1) Then
Sheets("Product Data").Range("U" & Cnt) = Sheets("MMDS Sheet").Cells(Cnt2, 3)
End If
End If
End If
End If
Next Cnt2
Next Cnt
End Sub