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

 

[Legacy 143009]

Hi Peter,

Surely there will be no difference between picking either of the same amount of matches since they both meets the condition and there is no way not know which one is which. So, in this case first match will be adequate.

Most of the time words will be unique. Let me tell you about my actual need. I have a list of products and sometimes product name varies in purchase list. So let say, my product neme is "Electrical Insulated Gloves" and, I have "Electrical Tape" and "Insulated Gloves" in my purchase list. I want to match the quantity of "Insulation Gloves" in this case.

Briefly, most of the time the words are unique and in order. But there can be some skipped words. So, "Electrical Gloves" is also possible. All I need is some kind of a partial match.

 

[Peter_SSs]

Does that mean that the column A list might have a fairly small number of words in a single cell, say max of 3 or 4 (or 5)?

 

[Legacy 143009]

To be more precise, it is very very unlikely to have both "Electrical Gloves" and "Electrical Work Glows" in the same purchase order so that type scenario can be neglected. But both have equal significance in general.

 

[Legacy 143009]

Does that mean that the column A list might have a fairly small number of words in a single cell, say max of 3 or 4 (or 5)?

It won't be safe to limit like that.

 

[Peter_SSs]

It won't be safe to limit like that.

Then in that case, with your version of Excel, I think that I would be pusuing a vba solution. Is that acceptable?
Also, your goal was to find the row with the highest number of word matches and return the value from column B. What should happen when there is not one clear winner like there has been in all your examples so far? For example ..

Flashbond.xlsm
	A	B	C	D
1	Electrical Insulated	1		Desired result:
2	Insulated Gloves	2	Electrical Insulated Gloves	??
3	Electrical Gloves	3
Sheet1

 

[Legacy 143009]

Flashbond.xlsm
	A	B	C	D
1	Electrical Insulated	1		Desired result:
2	Insulated Gloves	2	Electrical Insulated Gloves	??
3	Electrical Gloves	3
Sheet1

It is a very unlikely scenario. First match is ok. I can also write a vba also but your approaches always more efficient than mines. I will be looking forward to see. Thanks for your time and effort.

 

[Peter_SSs]

First match is ok.

OK, here is one way employing a UDF. If there is any punctuation involved that could introduce some problems.

Function LookupMost(r As Range, s As String) As Variant
  Dim RX As Object
  Dim a As Variant
  Dim i As Long, nMax As Long, n As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  a = r.Value
  For i = 1 To UBound(a)
    RX.Pattern = "\b(" & Replace(a(i, 1), " ", "|") & ")\b"
    n = RX.Execute(s).Count
    If n > nMax Then
      nMax = n
      LookupMost = a(i, 2)
    End If
  Next i
End Function

Flashbond.xlsm
	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
Sheet1

Cell Formulas
Range	Formula
D2	D2	=LookupMost(A1:B3,C2)
D6,D11	D6	=LookupMost(A5:B8,C6)

 

[Legacy 143009]

Any punctuation can be neglected. Thank you very much again!

 

[Peter_SSs]

Any punctuation can be neglected.

That is simple to say but not so simple to tell Excel how to do it.
Do you actually have any punctuation in your data? If so, could you post a few sets of representative sample data that shows the sort of punctuation variation that can occur?

 

[Legacy 143009]

It is all in Turkish Mostly "/", "-" and "."
Like:
Electrical Glowes L/XL
Or
Anti-static Rope 7.00MM

They are mostly the part of the word. So main delimeter is " ". Thats why I said "can be neglected".