• 396K Members
• 2,637 Online
• 432K Conversations
SOLVED

Highlighted
Deleted
Not applicable

# If cells equal then next returns

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

# Re: If cells equal then next returns

@Deleted

Use this formula in C1:

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

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

"Fruit","Not Fruit")

Solution

# Re: If cells equal then next returns

@Deleted this formula in C1 should work

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

# Re: If cells equal then next returns

Great, thank you - this worked!

# Re: If cells equal then next returns

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.

# Re: If cells equal then next returns

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

# Re: If cells equal then next returns

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.

# Re: If cells equal then next returns

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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies