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

 

[DanteAmor]

Try with this UDF:

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

HOW TO INSTALL UDFs
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use contains_words just like it was a built-in Excel function. For example:

Dante Amor
	A	B	C	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
Hoja1

Cell Formulas
Range	Formula
D2	D2	=contains_words(C2,A2:B4)

---

 

[Legacy 143009]

Thanks for your effort. I am looking for a pure formula solution if possible.

 

[DanteAmor]

In column B I put other numbers to do my tests.
Try:

Dante Amor
	A	B	C	D
1	The quick brown	12	Given sentence:	Desired result:
2	fox	13	The quick brown fox jumps over the lazy dog.	14
3	jumps over the lazy	14
4	dog	15
5	some words to compare	16
Hoja1

Cell Formulas
Range	Formula
D2	D2	=INDEX(B1:B5,MAX(IF(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1= LARGE(IF(ISNUMBER(SEARCH(A1:A5,C2)),LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1),1), IF(IF(ISNUMBER(SEARCH(A1:A5,C2)),LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1),ROW(B1:B5)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 143009]

In column B I put other numbers to do my tests.
Try:

Dante Amor
	A	B	C	D
1	The quick brown	12	Given sentence:	Desired result:
2	fox	13	The quick brown fox jumps over the lazy dog.	14
3	jumps over the lazy	14
4	dog	15
5	some words to compare	16
Hoja1

Cell Formulas
Range	Formula
D2	D2	=INDEX(B1:B5,MAX(IF(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1= LARGE(IF(ISNUMBER(SEARCH(A1:A5,C2)),LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1),1), IF(IF(ISNUMBER(SEARCH(A1:A5,C2)),LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1),ROW(B1:B5)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Flowless

 

[Peter_SSs]

Does that mean your data could not be like this where all 6 of the words in cell A1 are in C2?

Flashbond.xlsm
	A	B	C	D
1	The quick brown over lazy dog	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
Sheet1

Cell Formulas
Range	Formula
D2	D2	=INDEX(B1:B5,MAX(IF(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1=LARGE(IF(ISNUMBER(SEARCH(A1:A5,C2)),LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1),1),IF(IF(ISNUMBER(SEARCH(A1:A5,C2)),LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1),ROW(B1:B5)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 143009]

Does that mean your data could not be like this where all 6 of the words in cell A1 are in C2?

Flashbond.xlsm
	A	B	C	D
1	The quick brown over lazy dog	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
Sheet1

Cell Formulas
Range	Formula
D2	D2	=INDEX(B1:B5,MAX(IF(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1=LARGE(IF(ISNUMBER(SEARCH(A1:A5,C2)),LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1),1),IF(IF(ISNUMBER(SEARCH(A1:A5,C2)),LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5," ",""))+1),ROW(B1:B5)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Oh, I've never thought of that scenario. Yes, it should be any of the words. In your scenario, the desired result should be 1. Tnaks a lot @Peter_SSs ! Actually, I haven't checked @DanteAmor 's suggestion against this scenario.

 

[Peter_SSs]

Then in that case I suspect you will need a macro.
There is a further question though. In the examples so far all of the words in each cell in column A exist in the C2 value. Is that always the case or could you have an example like this where the words "red and "cat" in column A do not exist in C2?

Flashbond.xlsm
	A	B	C
1	The quick red	1	Given sentence:
2	cat	2	The quick brown fox jumps over the lazy dog.
3	jumps over the lazy	3
4	dog	4
Sheet1

 

[Legacy 143009]

Oh yes. You always look through the pinhole @Peter_SSs ! A more precise example would be:

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

I think the example above is only achievable via VBA.

If the words are in order, is still VBA required? Like the example below:

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

 

[Peter_SSs]

I'm not sure that I will have an answer for you either way, but I can see more possible confusion - or at least different ways to interpret things.
For the sample below

1. Does A1 have two matches or one for "quick"?
2. Does A2 have two matches or one for "the" (since "the" occurs twice in C2)?
3. Does A3 have two matches or one for "the" (there are certainly two of each in each cell but perhaps it is only considered as one word)?

Flashbond.xlsm
	A		C
1	A quick cat and a quick rat		Given sentence:
2	The slow horse		The quick brown fox jumps over the lazy dog.
3	The cat and the rat
Sheet1