0

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

1
0

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:

7
  • 1
    Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Aug 21 '20 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. – Ben Dickey Aug 22 '20 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". – Ben Dickey Aug 22 '20 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. – marikamitsos Aug 25 '20 at 8:16
  • I did, but haven't received responses to my notes about trying the above – Ben Dickey Aug 26 '20 at 16:09
0

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")
2
  • 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". – Ben Dickey Aug 22 '20 at 16:17
  • you could expand the function to use Find support.google.com/docs/answer/3094126?hl=en – 24601 Aug 23 '20 at 14:44

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.