Lookup for cell contains most of the words in a sentence.

[Legacy 143009]

Hello everyone,

I am looking for a formula input to D2. The aim is to find the cell in Column A that contains most of the words given in C2 and return Column B value.

	Column A	Column B	Column C	Column D
1	The quick brown	1	Given sentence:	Desired result:
2	fox	2	The quick brown fox jumps over the lazy dog.	3
3	jumps over the lazy	3
4	dog	4

 

[Peter_SSs]

Mostly "/", "-" and "."

They are mostly the part of the word.

You cannot tell vba to "mostly" do something. Those three punctuation symbols all have a special meaning in regular expression syntax, which is what I am using. They would need special treatment to deal with them but to determine the best way to do that I would need to see some good representative sample data and know all non-alphabetical and non-numerical characters that can occur as some may require different treatment.

I would also like to know whether any such punctuation does or can occur in the column A values and/or the column C values in the layout that we have been using.

Looks like numerical data is now also to be considered - not mentioned before. If "7.00MM" occurred in both column A and C2 do you consider that a "word" when counting how many words are found?

Any chance that we could still have this?

could you post a few sets of representative sample data that shows the sort of punctuation variation that can occur?

 

[bebo021999]

Stick to your 1st sample
An approach with UDF (User define formula)
Right click on tab name, View code, Insert, Module then paste below code into:

Option Explicit
Function BestMatch(rng As Range, cell As Range)
Dim i&, pos&, max&, count&, ce As Range, sA, st As String
For Each ce In rng
    sA = Split(ce): count = 0: pos = 0
    For i = 0 To UBound(sA)
        pos = InStr(pos + 1, ce, sA(i))
        If pos > 0 Then count = count + 1
    Next
    If count > max And count = UBound(sA) + 1 Then
        max = count
        'BestMatch = ce.Value ===> if expect text string
        'BestMatch = ce.Address(0, 0) ===> if expect cell address
        BestMatch = ce.Row '===> if expect row index
    End If
Next
End Function

I leave 3 options of outcome: Textstring, cell address or cell row
BestMatch(rng As Range, cell As Range)
Assums part string in A1:A4 (rng), original string in C2 (cell)
In D2, type:

=BestMatch($A$1:$A$4,$C$2)

 

[DanteAmor]

Hi @Flashbond , If you end up going with a UDF, you could check the UDF from post #2 again, I just made a few small changes with the new information you provided:

Function contains_words(rng As Range, txt As String)
  Dim c As Range
  Dim nmax As Long, n As Long
  Dim w As Variant
  
  For Each c In rng.Columns(1).Cells
    n = 0
    For Each w In Split(c.Value, " ")
      If InStr(1, txt, w) > 0 Then
        n = n + 1
        If n > nmax Then
          nmax = n
          contains_words = c.Offset(, 1).Value
        End If
      End If
    Next
  Next
End Function

Dante Amor
	A	B	C	D
1	Electrical	1		Result
2	Insulated Gloves	2	Electrical Insulated Gloves	2
3	Electrical Gloves	3
4
5	The quick brown over lazy dog	1		Result
6	fox	2	The quick brown fox jumps over the lazy dog.	1
7	jumps over the lazy	3
8	dog	4
9
10	jumps over the lazy	1		Result
11	fox	2	The quick brown fox jumps over the lazy dog.	3
12	The quick brown over lazy dog	3
13	dog	4
Hoja1

Cell Formulas
Range	Formula
D2	D2	=contains_words(A1:A3,C2)
D6,D11	D6	=contains_words(A5:A8,C6)

If there is any case that the UDF does not comply with, then I join Peter's request:

Any chance that we could still have this?

could you post a few sets of representative sample data that shows the sort of punctuation variation that can occur?