Forum Discussion
If cells equal then next returns
Could someone help me with a formula to produce the below:
- If cell A1 has the word “apple”
- and then cell B1 has the word “orange”
- return the word “fruit” in cell C1
Thanks
Deleted this formula in C1 should work
=IF(AND(B1="orange",A1="apple"),"fruit","not fruit")
7 Replies
Another formula:
= AND( COUNTIFS( products, {"*apple*";"*orange*"} ) )
This tests whether "apple" is to be found in either cell, returning 0, 1, or 2. Then it tests the second element of the array constant to determine whether "orange" is to be found in neither, one or both, again giving 0, 1, or 2. The AND only returns TRUE if neither element of the resulting array is zero.
A byproduct of such an approach is that
orange juice pineapple returns true irrespective of their order.
Deleted this formula in C1 should work
=IF(AND(B1="orange",A1="apple"),"fruit","not fruit")
- The required formula was premised on whether A1 HAS “apple” and B1 HAS “orange”, which must be construed as A1 INCLUDES “apple” and B1 INCLUDES “orange”.
If the premise of the formula would have been A1 IS “apple” and B1 IS “orange”, then such must be construed as A1 ONLY CONTAINS “apple” and B1 ONLY CONTAINS “orange”.
In stating that A1 HAS the word “apple”, you inevitably meant that A1 may also INCLUDE other characters other than the word “apple”, such that “pineapple”, “star apple”, or “apple juice” would have been also a valid content for A1.
Similarly, “sour orange”, sweet orange”, or “orange juice” would have been likewise a valid content for B1.
I hope you’ll appreciate the difference.
Deleted
Use this formula in C1:
=IF(ISNUMBER(SEARCH("apple",A1))*
ISNUMBER(SEARCH("orange",B1)),
"Fruit","Not Fruit")
