Help with type of formula required

I have a column containing text in each cell, sometimes with numerous words.

I have a number of categories and a list of key words for each of these categories. I would like to apply one of these categories to each cell (say in the adjacent cell) if one of the keywords appears in any cell.


For example

I have two categories, fruit and vegetables.
Under each category I have listed all the possible types of fruit and veg (this would be in a separate sheet).

In another sheet in column A I have lots of cells all containing text.

e.g. one cell may be ‘I went to the shop and bought some apples’. I would like to write a formula that could then return ‘Fruit’ in the cell adjacent to this one.

What sort formula do I need for this?

If you list all fruits and name the range Fruit then you can use this formula in B1, where data to search is in A1

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(fruit,A1))),"Fruit","")

with another range called Veg.....

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(fruit,A1))),"Fruit",IF(SUMPRODUCT(--ISNUMBER(SEARCH(Veg,A1))),"Veg",""))

Note: this won't accommodate both Fruit and Veg in one cell, if you want that then try

=TRIM(IF(SUMPRODUCT(--ISNUMBER(SEARCH(fruit,A1))),"Fruit"," ")&" "&IF(SUMPRODUCT(--ISNUMBER(SEARCH(Veg,A1))),"Veg",""))

....also the fruit won't need to be distinct words. If Grape is listed as a fruit you'll get a match with Grapefruit......although you can change the formulas to accommodate that too.......

Thanks daddy long legs.

I am however going to be looking to return a result from multiple ranges. How do I change the second formula in your response to if, for example, I have third and fourth ranges called ‘Meats’ and ‘Breads’?

You should be able to keep concatenating SUMPRODUCT formulas as per my example, e.g.

=TRIM(IF(SUMPRODUCT(--ISNUMBER(SEARCH(fruit,A1))),"Fruit"," ")&" "&IF(SUMPRODUCT(--ISNUMBER(SEARCH(Veg,A1))),"Veg","")&" "&IF(SUMPRODUCT(--ISNUMBER(SEARCH(meats,A1))),"Meats"," ")&" "&IF(SUMPRODUCT(--ISNUMBER(SEARCH(breads,A1))),"Breads",""))

Are you expecting a single cell to contain values from more than one category?