1

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

Improve this question
1

2 Answers 2

Reset to default
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:

Improve this answer
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, 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.
    – Ben Dickey
    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".
    – Ben Dickey
    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.
    – marikamitsos
    Aug 25, 2020 at 8:16
  • I did, but haven't received responses to my notes about trying the above
    – Ben Dickey
    Aug 26, 2020 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")
Improve this answer
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, 2020 at 16:17
  • you could expand the function to use Find support.google.com/docs/answer/3094126?hl=en
    – Web App User
    Aug 23, 2020 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.