Google-sheets – In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column

formulasgoogle sheetsgoogle-sheets-arrayformularegex

In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly.
So if the value in a col A cell = apple, banana or pineapple, it should return a value of Fruit in col B for that item.
Or if the value of a cell in col A = truck or motorcycle, it should return Vehicle in col B.

Haven't been able to figure this one out

ell

Best Answer

You can use the following formula

=ArrayFormula(IFNA(IFS(REGEXMATCH(A2:A,"Banana| pineapple|Apple")=true,"Fruit", 
                        REGEXMATCH(A2:A,"Truck|Motorcycle")=true,"Vehicle")))

enter image description here

Functions used:

Related Topic