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

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

– Rubén
Aug 22, 2020 at 19:16

You can use the following formula

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

Functions used:

• Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. Aug 21, 2020 at 16:31
• Do you know why it's only returning the expected values in B2-3 and not B4-9? (screenshot here: imgur.com/a/DBTl3RE). Also - is it true that I'm not able to manually edit any of the cells in column B now without stopping the formula? What I'd ideally looking to do is run a formula over a long column, have it return values for the applicable scenarios I code, and then for the cells that don't have an applicable automated answer, fill those in manually. Aug 22, 2020 at 16:08
• Also, I should have specified I'm looking to have match word (apple or banana, etc) just be included in the cell being reviewed - NOT exactly match. I.e. "Green Apple" or "Apple" or "Apples" would all return "Fruit". Aug 22, 2020 at 16:18
• Have you tried the given solution? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. Aug 25, 2020 at 8:16
• I did, but haven't received responses to my notes about trying the above Aug 26, 2020 at 16:09

A simpler (but less scaleable) alternative would be to use IF(OR(<"string1";<"string2";);(;))

So, in your target cell (D4 in this case) type the following formula:

``````=IF(OR(A4="truck";"motorcycle");"vehicle")
``````
• When I tried this formula, it worked on the first cell, but gave the error seen here (imgur.com/a/iEFnDBG) for the rest... I'm looking to have a formula consider multiple criteria at once for a long column of data. If it's an apple or banana, it's a fruit, but if it's a truck or car or motorcycle, it's a vehicle, or if it's a pen or pencil, it's a writing instrument. Also - I should have specified I'm looking to have match word (apple or banana, etc) just be included in the cell being reviewed - NOT exactly match. I.e. "Green Apple" or "Apple" or "Apples" would all return "Fruit". Aug 22, 2020 at 16:17
• you could expand the function to use Find support.google.com/docs/answer/3094126?hl=en Aug 23, 2020 at 14:44