SOLVED
Home

If cells equal then next returns

%3CLINGO-SUB%20id%3D%22lingo-sub-785506%22%20slang%3D%22en-US%22%3EIf%20cells%20equal%20then%20next%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785506%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3ECould%20someone%20help%20me%20with%20a%20formula%20to%20produce%20the%20below%3A%3CBR%20%2F%3E%3CBR%20%2F%3E-%20If%20cell%20A1%20has%20the%20word%20%E2%80%9Capple%E2%80%9D%3CBR%20%2F%3E-%20and%20then%20cell%20B1%20has%20the%20word%20%E2%80%9Corange%E2%80%9D%3CBR%20%2F%3E-%20return%20the%20word%20%E2%80%9Cfruit%E2%80%9D%20in%20cell%20C1%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-785506%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785527%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cells%20equal%20then%20next%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785527%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386299%22%20target%3D%22_blank%22%3E%40nicolanicola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20this%20formula%20in%20C1%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(ISNUMBER(SEARCH(%22apple%22%2CA1))*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EISNUMBER(SEARCH(%22orange%22%2CB1))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22Fruit%22%2C%22Not%20Fruit%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785529%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cells%20equal%20then%20next%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386299%22%20target%3D%22_blank%22%3E%40nicolanicola%3C%2FA%3E%26nbsp%3Bthis%20formula%20in%20C1%20should%20work%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(B1%3D%22orange%22%2CA1%3D%22apple%22)%2C%22fruit%22%2C%22not%20fruit%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785557%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cells%20equal%20then%20next%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785557%22%20slang%3D%22en-US%22%3EGreat%2C%20thank%20you%20-%20this%20worked!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786284%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cells%20equal%20then%20next%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786284%22%20slang%3D%22en-US%22%3EThe%20required%20formula%20was%20premised%20on%20whether%20A1%20HAS%20%E2%80%9Capple%E2%80%9D%20and%20B1%20HAS%20%E2%80%9Corange%E2%80%9D%2C%20which%20must%20be%20construed%20as%20A1%20INCLUDES%20%E2%80%9Capple%E2%80%9D%20and%20B1%20INCLUDES%20%E2%80%9Corange%E2%80%9D.%3CBR%20%2F%3EIf%20the%20premise%20of%20the%20formula%20would%20have%20been%20A1%20IS%20%E2%80%9Capple%E2%80%9D%20and%20B1%20IS%20%E2%80%9Corange%E2%80%9D%2C%20then%20such%20must%20be%20construed%20as%20A1%20ONLY%20CONTAINS%20%E2%80%9Capple%E2%80%9D%20and%20B1%20ONLY%20CONTAINS%20%E2%80%9Corange%E2%80%9D.%3CBR%20%2F%3EIn%20stating%20that%20A1%20HAS%20the%20word%20%E2%80%9Capple%E2%80%9D%2C%20you%20inevitably%20meant%20that%20A1%20may%20also%20INCLUDE%20other%20characters%20other%20than%20the%20word%20%E2%80%9Capple%E2%80%9D%2C%20such%20that%20%E2%80%9Cpineapple%E2%80%9D%2C%20%E2%80%9Cstar%20apple%E2%80%9D%2C%20or%20%E2%80%9Capple%20juice%E2%80%9D%20would%20have%20been%20also%20a%20valid%20content%20for%20A1.%3CBR%20%2F%3ESimilarly%2C%20%E2%80%9Csour%20orange%E2%80%9D%2C%20sweet%20orange%E2%80%9D%2C%20or%20%E2%80%9Corange%20juice%E2%80%9D%20would%20have%20been%20likewise%20a%20valid%20content%20for%20B1.%3CBR%20%2F%3EI%20hope%20you%E2%80%99ll%20appreciate%20the%20difference.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786386%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cells%20equal%20then%20next%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786386%22%20slang%3D%22en-US%22%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20like%20the%20formula%20to%20capture%20if%20cells%20A1%20and%20B1%20contain%20the%20word%20Apple%20or%20orange.%20So%20if%20cell%20A1%20contains%20%E2%80%9CApple%20juice%E2%80%9D%20and%20cell%20B1%20contains%20%E2%80%9Corange%20juice%E2%80%9D%20I%20would%20like%20cell%20C1%20to%20still%20return%20the%20value%20in%20the%20original%20post%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786413%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cells%20equal%20then%20next%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786413%22%20slang%3D%22en-US%22%3EIn%20that%20case%2C%20you%20marked%20the%20wrong%20formula%20as%20the%20best%20response!%20The%20formula%20I%20suggested%20earlier%20returns%20the%20result%2C%20exactly%20according%20to%20the%20parameters%20I%20explained.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786458%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cells%20equal%20then%20next%20returns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786458%22%20slang%3D%22en-US%22%3E%3CP%3EAnother%20formula%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20AND(%20COUNTIFS(%20products%2C%20%7B%22*apple*%22%3B%22*orange*%22%7D%20)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EThis%20tests%20whether%20%22apple%22%20is%20to%20be%20found%20in%20either%20cell%2C%20returning%200%2C%201%2C%20or%202.%26nbsp%3B%20Then%20it%20tests%20the%20second%20element%20of%20the%20array%20constant%20to%20determine%20whether%20%22orange%22%20is%20to%20be%20found%20in%20neither%2C%20one%20or%20both%2C%20again%20giving%200%2C%201%2C%20or%202.%26nbsp%3B%20The%20AND%20only%20returns%20TRUE%20if%20neither%20element%20of%20the%20resulting%20array%20is%20zero.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EA%20byproduct%20of%20such%20an%20approach%20is%20that%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Eorange%20juice%3C%2FTD%3E%3CTD%3Epineapple%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Ereturns%20true%20irrespective%20of%20their%20order.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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")

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies