classify
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.