Search a string to find "whole word" from list

[colink2]

Column A contains strings
Column D Contains a list of words to search for

Column B returns true if whole word found

Only return true if whole word is found eg Dog not DogD
If not in list return a null cell

----
If possible I would a more detailed result
Column A contains strings
Column D Contains a list of words to search for
Colun E contains the category for the word in the list of words

Column B returns position of 1st character if word found
Column C returns the category of the whole word found

Fairly sure I will need a VBA fiction for this but beyond my abilities

Any help appreciated.

 

[colink2]

Too late to edit

Both lists could be any length, so it would be good to search the whole columns ignoring any blank cells (which are likley to be a t bottom)

 

[jtakw]

Hi,

According to your description, you can achieve the results with these formulas:

Book3.xlsx
	A	B	C	D	E
1		Position	Category
2	Big Ball	5	Toy	Dog	Animal
3	Basketball			Cat	Animal
4	Ballroom			Ball	Toy
5	Dog Down	1	Animal	Umbrella
6	DogDog				Fruit
Sheet731

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(LOOKUP(LEN(A2),SEARCH(" "&D$2:D$6&" "," "&A2&" ")),"")
C2:C6	C2	=IFERROR(LOOKUP(1,1/(SEARCH(" "&D$2:D$6&" "," "&A2&" ")),E$2:E$6),"")

 

[colink2]

Compared to everyone I know I am good at using Excel, until I come on this forum seeking help. It is then clear how little I know.
This place is so much more pleasant an experience vs seeking help at stack exchange.

Absolutely perfect answer. Copy & Paste and it works.

I so much prefer a formula vs using VBA.

@jtakw Your help is really appreciated

 

[Peter_SSs]

Another option should you wish to consider it.
Note that my formula and jtakw's will give different results if it is possible that more than one of the words from column D is found in a column A cell.
My formula will return the first word in the column A cell that appears in column D, jtakw's formula will return the last word in the column A cell that appears in column D
For example, in row 7 my formulas return 5/Animal whereas the earlier formula returns 9/Toy.
If multiple matching words are possible, perhaps one suits you better than the other? ?‍

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

21 02 05.xlsm
	A	B	C	D	E
1		Position	Category
2	Big Ball Dog	5	Toy	Dog	Animal
3	Basketball			Cat	Animal
4	Ballroom			Ball	Toy
5	Dog Down	1	Animal
6	DogDog
7	Big Dog Ball	5	Animal
Lookup

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(AGGREGATE(15,6,SEARCH(" "&D$2:D$4&" "," "&A2&" "),1),"")
C2:C7	C2	=IF(B2="","",VLOOKUP(MID(A2,B2,FIND(" ",A2&" ",B2)-B2),D$2:E$4,2,0))

 

[jtakw]

You're welcome, glad to help and thanks for the feedback.

PS: Good to see and hear from you Peter

 

[Peter_SSs]

PS: Good to see and hear from you Peter

You too. I hadn't seen you about here for quite a while.