 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008If Statement with Two Different Logical Test
Hi there. How would my if statement look like if I my logical test can be more than 1 value? For example, if cell A1 = "apple" or "oranges", then return 1, else return 2. How can I show the apple or oranges logical test in equation form? Thank you! Try this... =IF(OR(A1={"apple","orange"}),1,2) Note: I left both of these singular even though you made 'apple' singular and 'oranges' plural. If you need that mixture, just add the 's' onto the end of 'orange'. The key is, put all the text you want the cell to match inside the curly braces, quoted and comma delimited. Rick > Hi there. > [quoted text clipped - 6 lines] > > Thank you! Great! Not having to specify the reference cell "A1" at each instance is efficient. Thanks for your time! > Try this... > [quoted text clipped - 17 lines] > > > > Thank you! Use the OR function such as =If(OR(A1="apple",A1="oranges"),1,2)  SignatureBest Regards, Luke M > Hi there. > [quoted text clipped - 5 lines] > > Thank you! Thank you Luke. I was hoping there was I way I didn't have to specify the "A1" reference cell at each instance...but hey, it works! Thanks for your time. > Use the OR function such as > =If(OR(A1="apple",A1="oranges"),1,2) [quoted text clipped - 8 lines] > > > > Thank you! Try one of these: =IF(OR(A1="apple",A1="orange"),1,2) =IF(OR(A1={"apple","orange"}),1,2) C1 = apple C2 = orange =IF(OR(A1=C1,A1=C2),1,2) As an array formula** : =IF(OR(A1=C1:C2),1,2) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) SignatureBiff Microsoft Excel MVP > Hi there. > [quoted text clipped - 6 lines] > > Thank you! This is great! Thanks Biff! > Try one of these: > [quoted text clipped - 24 lines] > > > > Thank you! Hi Bill... What if...using the array method, I do have a list but I only want a select few on that list? For example: C1 = apple; C2 = oranges; C3 = grapes. I want my logical test to match either apple or grape, can I use this method? I tried to edit the formula to show instead of =IF(OR(A1=C1:C2),1,2) I did =IF(OR(A1=C1,C3),1,2)For some reason, even if A1 = C3, it still returned 2. (note: when A1 = C1, it returned 1). Thanks > Try one of these: > [quoted text clipped - 24 lines] > > > > Thank you! >can I use this method? Not without making it overly complicated! Array entered: =IF(OR(A1=T(OFFSET(C1:C3,{0,2},,))),1,2) D1 = 0 D2 = 2 =IF(OR(A1=T(OFFSET(C1:C3,D1:D2,,))),1,2) So, for practical purposes, I would not use these! SignatureBiff Microsoft Excel MVP > Hi Bill... > [quoted text clipped - 40 lines] >> > >> > Thank you! Gocha! Thanks again Biff. > >can I use this method? > [quoted text clipped - 55 lines] > >> > > >> > Thank you! You're welcome! SignatureBiff Microsoft Excel MVP > Gocha! Thanks again Biff. > [quoted text clipped - 61 lines] >> >> > >> >> > Thank you! =2-OR(A1="Apple",A1="Oranges") > Hi there. > [quoted text clipped - 5 lines] > > Thank you! Thanks Aladin! > =2-OR(A1="Apple",A1="Oranges") > [quoted text clipped - 7 lines] > > > > Thank you! I would be tempted to use a vlookup, create a table with your fruit in the first column and a number in the second. then use formula =vlookup (a1, fruittable, 2,0) > Hi there. > [quoted text clipped - 5 lines] > > Thank you! =NOT(OR(A1={"apple","oranges"}))+1 > Hi there. > [quoted text clipped - 5 lines] > > Thank you! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.