How to classify fruit into different categories (Orange, Strawberry, Durian)

Kay: What’s the formula to detect the word “orange” and returns a number?

Jason: =IF(IISNUMBER(SEARCH(“orange”,A1),1,””)

A! contains the description. SEARCH worksheet function returns a value if the word is found. If not, it returns an #VALUE! Error. You need to use ISNUMBER to return no results. IF worksheet function is to return the number 1 if found and blank “” if not found

You need to use this if you have hundreds and thousands of rows and you want the answer fast.

Kay: What if there is more than one word, either orange or strawberry?

Jason: =IF(IISNUMBER(SEARCH(“orange”,A1),1,””) + IF(IISNUMBER(SEARCH(“strawberry”,A1),1,””)

Jason: You can use two of the same formula and add them together. If you get strawberry or orange, get get 1. If you get both, you get 2.

Kay: 1 is a category and 2 is another category.

Jason: Then you can use

=IF(ISNUMBER(SEARCH(“orange”,D4)),1,IF(ISNUMBER(SEARCH(“strawberry”,D4)),2,””))

Kay: What if I have many words for the orange category, blood, California, Sunkist, Jaffar, etc? And another set of words for the strawberry category? And a third set for durian?

Jason: The formula will become very long. Need to change to this.

=IFERROR(INDEX($H$5:$H$12,MATCH(1,COUNTIF(C4,”“&$G$5:$G$12&”“),0)),””)

This problem can be solved with a long formula or use Power Query to automate it. Learn how to do it in Our Courses.