I'm looking for a formula to use in google sheets that creates a boolean when the conditions "House" and "Car" are both found in the other columns so
| Name | Priority | Priority | Priority | Boolean in question |
|---|---|---|---|---|
| John | House | Car | Loans | |
| Ned | House | Groceries | Car | |
| Dom | Family | Car | Going Fast | |
| Thanos | Stones | Balance | House | |
| Homer | Donuts | Car | House |
would become
| Name | Priority | Priority | Priority | Boolean in question |
|---|---|---|---|---|
| John | House | Car | Loans | Yes |
| Ned | House | Groceries | Car | Yes |
| Dom | Family | Car | Going Fast | No |
| Thanos | Stones | Balance | House | No |
| Homer | Donuts | Car | House | Yes |
How can I write a formula to create this outcome.
CodePudding user response：
Try:
=INDEX(IF(MMULT(ArrayFormula(REGEXMATCH(B2:D6,"Car") (REGEXMATCH(B2:D6,"House"))),{1;1;1})=2,"Yes","No"))
A slight modification if the search words in one row can be repeated:
=INDEX(IF(((MMULT(REGEXMATCH(B2:D6,"Car")*1,{1;1;1})>0) (MMULT(REGEXMATCH(B2:D6,"House")*1,{1;1;1})>0))=2,"Yes","No"))