Excel search multiples rows containing substring-CodePudding

I have an Excel file with 2 sheets :

The first one got a list of keywords in a column.
The second one got sentences on a column along with an id on another column.

Thus the 2 sheets look like this :

Sheet 1:                         Sheet 2:
   A                      A             B
| the |               | 15587 | The cat is walking |
| cat |               | 94683 | No one here        |
| ... |               | 47222 | The TV is on       |
                      | 59378 | No cat allowed     |
                      |  ...  | ...                |

What I want to do is to put on the B column of sheet 1 the list of sentences ids where the keyword is found. So here I'll get on sheet 1 :

   A         B
| the | 15587;47222 |
| cat | 15587;59378 |
| ... |     ...     |

Do you know how I can achieve this using functions ? I tried VLOOKUP but it only returns the first occurrence and I don't know how to use FILTER with an operator to check if the sentence contain a string.

Thanks

CodePudding user response：

You could try:

Formula in E1:

=TEXTJOIN(";",,FILTER(A$1:A$4,ISNUMBER(SEARCH(" "&D1&" "," "&B$1:B$4&" ")),""))

CodePudding user response：

I think it's not possible with formules, do you need to create a function. You can read this https://www.extendoffice.com/documents/excel/3309-excel-vlookup-multiple-values-concatenated.html, i think can help.