I need to match a word to a list of words and then insert the category name in the cell next to it.

Eg. I will in Column A type a list of words like Apple, Red Apple, Coke, Carrot The formula must then find the word (or part of the word) in a List Column D and the place the Category in cell B next to the word.

enter image description here

2 Answers 2


you have two slightly different options - choose which fits your needs most:

  • generate category via FILTER()
  • generate category via REGEXMATCH()

SPREADSHEET: https://docs.google.com/spreadsheets/d/

GIF: https://i.imgur.com/FZwW2o0.gif (open in new window)


this also does what you need but it has its own limitations...



  • Thank you..Working 100% but only 1 question: If the Words are "Sweet Red Apple" then I would like the formula to see the word "Apple" and then compare to List so it would then place "Fruit" in Type box. Meaning the formula should see a part of the word and not the complete words
    – Jaco Nel
    Jan 10, 2019 at 8:23
  • @JacoNel hence those "limitations" I mentioned... provided formula works only if there are max 2 words. to add 3 word support you will need to add 2nd row again to index out 3rd virtual column or implement some IF() nesting
    – user0
    Jan 10, 2019 at 8:42
  • @JacoNel webapps.stackexchange.com/help/someone-answers
    – user0
    Jun 9, 2019 at 0:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.