Issues with matches for Partial Text

[danhenshy23]

Hi all,

In Column A I have a list of text strings which I am wanting to search.
In Column C I have a list of terms I want to Search for
In Column D I have the categories of each of those search terms
In Column B I want to show the category that the search term belongs to

The problem I am having is that I am getting a match for a category where the word I am searching for exists within part of another word. For example if "hot" is the word I am searching for, I will get a match for the word "hotter".

How do I overcome this? Here is the formula I am using currently (entered into column B):

{=INDEX($D$2:$D$10,MATCH(TRUE,ISNUMBER(SEARCH($C2:C10,$A1)),0))}

 

[AhoyNC]

Try:

Book1
	A	B	C	D
1	String	category	Search Words	Cat. List
2	It's hot today	Cat1	hot	Cat1
3	It's getting hotter	No Match	apple	Cat2
4	An apple a day	Cat2
5	Get some applesauce	No Match
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=IFNA(INDEX($D$2:$D$3,MATCH(TRUE,ISNUMBER(SEARCH(" "&$C$2:$C$3&" "," "&A2&" ")),0)),"No Match")