Compare 2 lists, look for substrings from List1 in larger strings in List2

[svendfj]

Hello! I have a Workbook that is updated every week with a list, in column A on Sheet1. I

want to go through the Sheet1 list one cell at a time, and look for matches in Sheet2, column A, and copy the text from that cell on Sheet2 into column B for the current row in Sheet1.

The Sheet1 strings are a subset of the Sheet2 strings. For example, I would take "red cat" from a cell in Sheet1 and be able to find a match in a Sheet2 cell that contains 'small red cat and brown dog". ​

​

<strike></strike>

My research has not revealed existing programs that can easily be leveraged.

Thank you in advance for any perspective you can please share. ​

Sven

 

[Aladin Akyurek]

Post 5 rows from list 1 and after that 7 rows from list 2 along with expected results for easier explanation.

 

[svendfj]

Thank you very much Aladin! Below are the requested examples. I want to check, for each cell/string in LIST 1 on Sheet1, to look for that string in LIST 2 on Sheet2, and then if there is a match, copy that cell into Sheet1, Column B.

LIST 1[TABLE="width: 99"]
<tbody>[TR]
[TD]Newt[/TD]
[/TR]
[TR]
[TD]King Kangaroo[/TD]
[/TR]
[TR]
[TD]Bobby Bee[/TD]
[/TR]
[TR]
[TD]Horatio Hare[/TD]
[/TR]
[TR]
[TD]Otto Owl[/TD]
[/TR]
[TR]
[TD]Queen Bee[/TD]
[/TR]
</tbody>[/TABLE]

LIST 2
[TABLE="width: 363"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 363"]
<colgroup><col></colgroup><tbody>[TR]
[TD]He was surprised when suddenly a King Kangaroo ran close by[/TD]
[/TR]
[TR]
[TD]And sitting astride a twig of a bush Bobby bee. Ostensibly motionless, the Horatio Hare was trembling with[/TD]
[/TR]
[TR]
[TD]"The Horatio Hare has lost his spectacles," well, he began to wonder.[/TD]
[/TR]
[TR]
[TD]Otto Owl loved to rest quietly whilst no one was watching.[/TD]
[/TR]
[TR]
[TD]Newt knew too much to be stopped by so small a problem[/TD]
[/TR]
[TR]
[TD]You can take him in your pouch.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>DESIRED RESULT
[TABLE="width: 499"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Newt[/TD]
[TD]Newt knew too much to be stopped by so small a problem[/TD]
[/TR]
[TR]
[TD]King Kangaroo[/TD]
[TD]He was surprised when suddenly a King Kangaroo ran close by[/TD]
[/TR]
[TR]
[TD]Bobby Bee[/TD]
[TD]And sitting astride a twig of a bush Bobby bee. Ostensibly motionless, the Horatio Hare was trembling with[/TD]
[/TR]
[TR]
[TD]Horatio Hare[/TD]
[TD]"The Horatio Hare has lost his spectacles," well, he began to wonder.[/TD]
[/TR]
[TR]
[TD]Otto Owl[/TD]
[TD]Otto Owl loved to rest quietly whilst no one was watching.[/TD]
[/TR]
[TR]
[TD]Queen Bee[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike><strike></strike>
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

If there won't be More than 1 match, this will work.
If there's more than 1 match, this will return the Last one.

Book1
	A	B
1	Newt	Newt knew too much to be stopped by so small a problem
2	King Kangaroo	He was surprised when suddenly a King Kangaroo ran close by
3	Bobby Bee	And sitting astride a twig of a bush Bobby bee. Ostensibly motionless, the Horatio Hare was trembling with
4	Horatio Hare	"The Horatio Hare has lost his spectacles," well, he began to wonder.
5	Otto Owl	Otto Owl loved to rest quietly whilst no one was watching.
6	Queen Bee
Sheet126

Cell Formulas
Range	Formula
B1	=IFERROR(LOOKUP(2,1/SEARCH(A1,Sheet130!A$1:A$6),Sheet130!A$1:A$6),"")

Book1
	A
1	He was surprised when suddenly a King Kangaroo ran close by
2	And sitting astride a twig of a bush Bobby bee. Ostensibly motionless, the Horatio Hare was trembling with
3	"The Horatio Hare has lost his spectacles," well, he began to wonder.
4	Otto Owl loved to rest quietly whilst no one was watching.
5	Newt knew too much to be stopped by so small a problem
6	You can take him in your pouch.
Sheet130

Sheet126 B1 formula copied down, change Sheet names to match yours, adjust Cell references/range as needed.

 

[svendfj]

Thank you very much Aladin! It works perfectly. Please let me ask; if I wanted to expand the area on Sheet2 that is searched - say into columns B and C, what would be the best way to do so?

This formula, for example, does not work:
=IFERROR(LOOKUP(2,1/SEARCH(A1,Sheet2!A$1:C$8),Sheet2!A$1:C$8),"")

Thanks again!

 

[jtakw]

Errrr, it wasn't Aladin.

Please explain what you mean by "expand to columns B and C", are you saying to return results from All 3 columns? or something else? Please clarify, may be even show some samples.

 

[svendfj]

Thanks jtakw. I just was curious of how your elegant solution could best be modified to allow for a case where the list to be searched is in 3 columns instead of 1. I could just use your formula to search each column separately, but I am curious what the best way to search all 3 columns at once is. Also, please let me ask if there is a simple way to return the row or cell address of the cell in Sheet2 that contains the string from Sheet1.

Thanks jtakw. I appreciate it​

 

[jtakw]

Ok, I can't think of a "simple" way to include 3 Columns (A, B, C) for this task, so we can just Nest the Lookups:

Book1
	A	B	C
1	Newt	Newt knew too much to be stopped by so small a problem	B5
2	King Kangaroo	He was surprised when suddenly a King Kangaroo ran close by	C1
3	Bobby Bee	And sitting astride a twig of a bush Bobby bee. Ostensibly motionless, the Horatio Hare was trembling with	A2
4	Horatio Hare	"The Horatio Hare has lost his spectacles," well, he began to wonder.	A3
5	Otto Owl	Otto Owl loved to rest quietly whilst no one was watching.	A4
6	Queen Bee
Sheet126

Cell Formulas
Range	Formula
B1	=IFERROR(IFERROR(IFERROR(LOOKUP(2,1/SEARCH(A1,Sheet130!A$1:A$6),Sheet130!A$1:A$6),LOOKUP(2,1/SEARCH(A1,Sheet130!B$1:B$6),Sheet130!B$1:B$6)),LOOKUP(2,1/SEARCH(A1,Sheet130!C$1:C$6),Sheet130!C$1:C$6)),"")
C1	=IFERROR(IFERROR(IFERROR("A"&MATCH(B1,Sheet130!A$1:A$6,0),"B"&MATCH(B1,Sheet130!B$1:B$6,0)),"C"&MATCH(B1,Sheet130!C$1:C$6,0)),"")

Book1
	A	B	C
1			He was surprised when suddenly a King Kangaroo ran close by
2	And sitting astride a twig of a bush Bobby bee. Ostensibly motionless, the Horatio Hare was trembling with
3	"The Horatio Hare has lost his spectacles," well, he began to wonder.
4	Otto Owl loved to rest quietly whilst no one was watching.
5		Newt knew too much to be stopped by so small a problem
6	You can take him in your pouch.
Sheet130

Formulas copied down.
C1 formula gives you the Cell Address.

 

[svendfj]

Awesome jtakw!

 

[Aladin Akyurek]

Thank you very much Aladin! Below are the requested examples. I want to check, for each cell/string in LIST 1 on Sheet1, to look for that string in LIST 2 on Sheet2, and then if there is a match, copy that cell into Sheet1, Column B.

[...]

Thanks providing requested samples.

In B1 of Sheet1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&A1," "&Sheet2!$A$1:$A$6),Sheet2!$A$1:$A$6),"")

Note this formula picks out the string which occurs the latest in the target range, For this reason, it is recommended that you sort Sheet1 in ascending order.

It is probably better that Sheet2 does not contain any punctuation!...

Note. If Sheet2 contains more columns, expand Sheet2 with concatenation:

Suppose we have 3 columns in Sheet2, then in D2 enter and copy down:

=A1&" "&B1&" "&C1

The formula of Sheet1 should still work.