SOLVED

If cells equal then next returns

Deleted
Not applicable
Hello,
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
7 Replies

@Deleted 

Use this formula in C1: 

=IF(ISNUMBER(SEARCH("apple",A1))*

ISNUMBER(SEARCH("orange",B1)),

"Fruit","Not Fruit")

best response
Solution

@Deleted this formula in C1 should work

 

=IF(AND(B1="orange",A1="apple"),"fruit","not fruit")

Great, thank you - this worked!
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.
Thanks @Twifoo.

I would like the formula to capture if cells A1 and B1 contain the word Apple or orange. So if cell A1 contains “Apple juice” and cell B1 contains “orange juice” I would like cell C1 to still return the value in the original post
In that case, you marked the wrong formula as the best response! The formula I suggested earlier returns the result, exactly according to the parameters I explained.

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 juicepineapple

returns true irrespective of their order.

1 best response

Accepted Solutions
best response
Solution

@Deleted this formula in C1 should work

 

=IF(AND(B1="orange",A1="apple"),"fruit","not fruit")

View solution in original post