Find a keyword/phase from a table in an other table with an all or a partial match

Hi

I have two unrelated tables.

Table A:

ID Sentence
1 The bird, cat and dog are Jason's friendly pets.
2 My dog really likes to go to the dog park.
3 I have a very large, colourful and noisy bird.

Table B:

Keywords
bird
cat
dog
dog park
sheep
pets
park

If it's possible, I need help with a query or VBA that will ultimately give me the result below:

ID Sentence Match1 Match2 Match3 Match4
1 The bird, cat and dog are Jason's friendly pets. bird cat dog pets
2 My dog really likes to go to the dog park. dog dog park
3 My bird is a very large, colourful and noisy bird. bird bird

If there's no keyword matches in the sentence, the ID and sentence would still appear but all Match columns would be blank.

EDIT: The same word can appear more than once as in ID 3.

Would anyone know how I can achieve this? TIA

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H
1	ID	Sentence	Match 1	Match 2	Match 3	Match 4		Keywords
2	1	The bird, cat and dog are Jason's friendly pets.	bird	cat	dog	pets		bird
3	2	My dog really likes to go to the dog park.	dog	dog park	park			cat
4	3	I have a very large, colourful and noisy bird.	bird					dog
5								dog park
6								sheep
7								pets
8								park

	B	C
2	The bird, cat and dog are Jason's friendly pets.	=IFERROR(INDEX($H:$H,SMALL(IF(ISNUMBER(SEARCH($H$2:$H$8,$B2)),ROW($H$2:$H$8),10^9),COLUMN()-2)),"")

Sheet: Sheet1

Formula in C2:

Formula:

Please Login or Register  to view this content.

is array formula so you have to accept it with Ctrl+Shift+Enter (not just Enter), then drag it right and down.

EDIT: sorry I did not spot that question is about Access not excel.

Hi KOKOSEK, thanks for your quick reply, however I was wanting a MSAccess solution and not MSExcel. Have I put this in the wrong forum?

EDIT: Sorry just saw your edit

In a query you could use the Instr function.

https://www.techonthenet.com/access/...ring/instr.php


or use the IN clause

https://accessexperts.com/blog/2013/...ou-out-not-in/