SOLVED

How to add an OR statement to an already existing IF AND statement

%3CLINGO-SUB%20id%3D%22lingo-sub-1463536%22%20slang%3D%22en-US%22%3EHow%20to%20add%20an%20OR%20statement%20to%20an%20already%20existing%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1463536%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20below%20(and%20attached%20in%20column%20Q%20of%20'LIVE'%20tab)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(YEAR(AA%241)%26gt%3B%3DYEAR(%24F2)%2CYEAR(AA%242)%26lt%3B%3DYEAR(%24G2)%2CDATE(Q%241%2C1%2C1)%26lt%3B%26gt%3B%24G2)%2C%22LIVE%22%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20I%20need%20to%20build%20in%20an%20additional%20statement.%20Regardless%20of%20whether%20a%20contract%20is%20between%20the%20date%20range%20above%2C%20if%20a%20client%20is%20ACTIVE%20in%20a%20given%20year%20(by%20looking%20up%20the%20same%20Account%20name%20in%20the%20'ACTIVE'%20tab%20and%20if%20it%20says%20'ACTIVE'%20in%202017%2C%20then%20I%20want%20it%20to%20say%20LIVE%20anyway%20regardless%20of%20the%20dates.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20in%20column%20Q%20for%20account%20name%20Zista%20-%20this%20should%20say%20LIVE%2C%20even%20though%20the%20contract%20did%20not%20fall%20within%20the%20dates%2C%20if%20it%20is%20ACTIVE%2C%20then%20it%20must%20be%20LIVE%20no%20matter%20what.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Summary%2C%20if%20an%20account%20is%20ACTIVE%2C%20then%20%22LIVE%22%20else%20go%20by%20original%20formula%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20help%20it%20is%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJenny%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1463536%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1466552%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20an%20OR%20statement%20to%20an%20already%20existing%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1466552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3Bfollwing%20the%20summary%20you%20provided%20%3CSTRONG%3E%22if%20an%20account%20is%20ACTIVE%2C%20then%20%22LIVE%22%20else%20go%20by%20original%20formula%20above%22%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20be%20looking%20to%20construct%20a%20nested%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%20logic%20containing%20an%20%3CSTRONG%3EHLOOKUP%3C%2FSTRONG%3E%20function%20as%20shown%20below%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdded%20formula%20%3D%20IF(HLOOKUP(A2%2CACTIVE!%24E%244%3A%24I%24395%2C2)%20%3D%20%22Active%22%2C%20%22LIVE%22%2C%20Original_Formula_Above)%3C%2FP%3E%3CP%3E-%20Original%20formula%20%3D%26nbsp%3B%3CSPAN%3EIF(AND(YEAR(AA%241)%26gt%3B%3DYEAR(%24F2)%2CYEAR(AA%242)%26lt%3B%3DYEAR(%24G2)%2CDATE(Q%241%2C1%2C1)%26lt%3B%26gt%3B%24G2)%2C%22LIVE%22%2C%22%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20put%20together%2C%20you%20should%20have%20your%20final%20formula%20for%20%3CSTRONG%3EColumn%3C%2FSTRONG%3E%20%3CSTRONG%3EQ%3C%2FSTRONG%3E%20to%20look%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3DIF(HLOOKUP(A2%2CACTIVE!%24E%244%3A%24I%24395%2C%3CFONT%20color%3D%22%23FF6600%22%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%3C%2FFONT%3E)%20%3D%20%22Active%22%2C%20%22LIVE%22%2C%20IF(AND(YEAR(AA%241)%26gt%3B%3DYEAR(%24F2)%2CYEAR(AA%242)%26lt%3B%3DYEAR(%24G2)%2CDATE(Q%241%2C1%2C1)%26lt%3B%26gt%3B%24G2)%2C%22LIVE%22%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BAnd%20because%20we%20are%20looking%20at%20the%20%3CSTRONG%3E2017%20%3C%2FSTRONG%3Ein%20the%20%3CFONT%20color%3D%22%23FF6600%22%3E%3CFONT%20color%3D%22%23000000%22%3E%3CSTRONG%3EACTIVE%20sheet%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FFONT%3E%26nbsp%3Bthe%20hlookup%20function%20has%20the%20number%20%3CFONT%20color%3D%22%23FF6600%22%3E2%3CFONT%20color%3D%22%23000000%22%3E(2nd%20column%20in%20table%20in%20the%20%3CSTRONG%3EACTIVE%20sheet%3C%2FSTRONG%3E)%3C%2FFONT%3E%3C%2FFONT%3E.%20If%20we%20are%20to%20enter%20the%20same%20formula%20for%202018%2C%20the%20hlookup%20function%20would%20have%20the%20number%20%3CFONT%20color%3D%22%23FF6600%22%3E3%3CFONT%20color%3D%22%23000000%22%3E(3rd%20column%20in%20table%20in%20the%20ACTIVE%20sheet%3C%2FFONT%3E%3C%2FFONT%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1467098%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20an%20OR%20statement%20to%20an%20already%20existing%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1467098%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3BI%20am%20quite%20certain%20that%20the%20HLOOKUP%20function%20above%20will%20not%20work.%20However%2C%20using%20VLOOKUP%20like%20this%20will%20do%20what%20you%20are%20asking%20for.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(VLOOKUP(A2%2CACTIVE!%24A%241%3A%24E%24392%2C2%2CFALSE)%20%3D%20%22Active%22%2C%20%22LIVE%22%2C%3CORIGINAL%20formula%3D%22%22%3E)%3C%2FORIGINAL%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EBut%2C%20I%20can't%20resist%20indicating%20that%20I%20believe%20your%20original%20formula%20is%20overly%20complicated.%20There%20is%20no%20need%20for%20the%20helper%20dates%20in%20AA%20and%20further.%20In%2C%20the%20attached%20file%2C%20I%20have%20made%20some%20changes%20in%20most%20of%20your%20formulae.%20Furthermore%2C%20I%20chose%20a%20combination%20of%20INDEX%20and%20MATCH%20in%20stead%20of%20VLOOKUP.%26nbsp%3BNote%20that%20I%20used%20%22LIVE-1%22%20and%20%22LIVE-2%22%20in%20the%20formulae%20in%20columns%20Q%3AS%20to%20make%20it%20visible%20which%20%22LIVE%22%20the%20formula%20returns.%20The%20one%20from%20the%20INDEX%20function%20or%20the%20one%20from%20the%20second%20IF%20function.%20Once%20you%20are%20satisfied%20that%20this%20is%20correct%20you%20can%20change%20both%20to%20%22LIVE%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20your%20choice%20of%20course%2C%20if%20you%20want%20to%20use%20these%20changes%20or%20not.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470238%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20an%20OR%20statement%20to%20an%20already%20existing%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470238%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20I'm%20afraid%20I%20need%20to%20attach%20original%20report%20however%2C%20as%20I%20cannot%20seem%20to%20transfer%20the%20formula%20across%20correctly%3F%20I%20am%20happy%20to%20use%20either%20formula.%20As%20you%20can%20see%20from%20the%20excel%2C%20the%20'Active'%20Tab%20is%20actually%20called%20'Indirect'%20and%20has%20further%20info%20there.%20The%20report%20is%20an%20absolute%20nightmare%2C%20but%20this%20formula%20you%20have%20provided%20helps%20SO%20MUCH!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470665%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20an%20OR%20statement%20to%20an%20already%20existing%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3BSee%20attached.%20Must%20agree%20that%20this%20is%20not%20the%20most%20user%20friendly%20schedule%20I%20have%20seen.%20And%20I%20have%20not%20gone%20into%20the%20details.%20Just%20focussed%20on%20the%20formulae%20in%20columns%20Q%3AT%20on%20the%20%22Salesforce%22%20sheet.%20Please%20do%20check%20if%20this%20is%20indeed%20what%20you%20want.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470269%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20an%20OR%20statement%20to%20an%20already%20existing%20IF%20AND%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470269%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%2C%20I%20appreciate%20you%20taking%20the%20time%20to%20look%20at%20my%20query!%20I%20have%20not%20used%20a%20HLOOKUP%20before%2C%20so%20that's%20another%20arrow%20to%20add%20to%20my%20bow%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hello,

 

I have a formula below (and attached in column Q of 'LIVE' tab)

 

=IF(AND(YEAR(AA$1)>=YEAR($F2),YEAR(AA$2)<=YEAR($G2),DATE(Q$1,1,1)<>$G2),"LIVE","")

 

but I need to build in an additional statement. Regardless of whether a contract is between the date range above, if a client is ACTIVE in a given year (by looking up the same Account name in the 'ACTIVE' tab and if it says 'ACTIVE' in 2017, then I want it to say LIVE anyway regardless of the dates. 

 

For example in column Q for account name Zista - this should say LIVE, even though the contract did not fall within the dates, if it is ACTIVE, then it must be LIVE no matter what.

 

In Summary, if an account is ACTIVE, then "LIVE" else go by original formula above.

 

Thank you in advance for your help it is much appreciated!

 

Jenny

 

6 Replies
Highlighted

@JennySommet follwing the summary you provided "if an account is ACTIVE, then "LIVE" else go by original formula above" 

 

You might be looking to construct a nested IF logic containing an HLOOKUP function as shown below;

 

Added formula = IF(HLOOKUP(A2,ACTIVE!$E$4:$I$395,2) = "Active", "LIVE", Original_Formula_Above)

- Original formula = IF(AND(YEAR(AA$1)>=YEAR($F2),YEAR(AA$2)<=YEAR($G2),DATE(Q$1,1,1)<>$G2),"LIVE","")

 

When put together, you should have your final formula for Column Q to look something like this:

 =IF(HLOOKUP(A2,ACTIVE!$E$4:$I$395,2) = "Active", "LIVE", IF(AND(YEAR(AA$1)>=YEAR($F2),YEAR(AA$2)<=YEAR($G2),DATE(Q$1,1,1)<>$G2),"LIVE",""))

 

 And because we are looking at the 2017 in the ACTIVE sheet the hlookup function has the number 2(2nd column in table in the ACTIVE sheet). If we are to enter the same formula for 2018, the hlookup function would have the number 3(3rd column in table in the ACTIVE sheet.

 

Highlighted
Best Response confirmed by JennySommet (Contributor)
Solution

@JennySommet I am quite certain that the HLOOKUP function above will not work. However, using VLOOKUP like this will do what you are asking for.

=IF(VLOOKUP(A2,ACTIVE!$A$1:$E$392,2,FALSE) = "Active", "LIVE",<original formula>)

But, I can't resist indicating that I believe your original formula is overly complicated. There is no need for the helper dates in AA and further. In, the attached file, I have made some changes in most of your formulae. Furthermore, I chose a combination of INDEX and MATCH in stead of VLOOKUP. Note that I used "LIVE-1" and "LIVE-2" in the formulae in columns Q:S to make it visible which "LIVE" the formula returns. The one from the INDEX function or the one from the second IF function. Once you are satisfied that this is correct you can change both to "LIVE".

 

It's your choice of course, if you want to use these changes or not.

Highlighted

@Riny_van_Eekelen 

 

Thank you! I'm afraid I need to attach original report however, as I cannot seem to transfer the formula across correctly? I am happy to use either formula. As you can see from the excel, the 'Active' Tab is actually called 'Indirect' and has further info there. The report is an absolute nightmare, but this formula you have provided helps SO MUCH!

 

 

Highlighted
Thank you very much, I appreciate you taking the time to look at my query! I have not used a HLOOKUP before, so that's another arrow to add to my bow
Highlighted

@JennySommet See attached. Must agree that this is not the most user friendly schedule I have seen. And I have not gone into the details. Just focussed on the formulae in columns Q:T on the "Salesforce" sheet. Please do check if this is indeed what you want. 

 

Highlighted
Thank you so much - really appreciate your help! Yes it is a horrible report - waiting for a Tableau dashboard to be built, but it's on the roadmap for end of year, so until then must suffer with excel. In fact, I've already posted several times on the forum about this particular excel report - hopefully each answer will help others on the forum too!