Home

Need help with advanced IF/AND nested formula (too many arguments)

%3CLINGO-SUB%20id%3D%22lingo-sub-841215%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20advanced%20IF%2FAND%20nested%20formula%20(too%20many%20arguments)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841215%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI'm%20stuck%20with%20a%20fairly%20advanced%20(at%20least%20to%20me)%20nested%20IF%2FAND%20formula.%20I%20have%20a%20formula%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(B18%3D%22Onshore%20(US)%22%2CB8%3D%22Social%20Sciences%22%2CB35%3D%22Simple%22)%2CRate_Card!E20%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewith%20the%20first%20three%20arguments%20coming%20out%20of%20my%20current%20tab%2C%20and%20the%20last%20coming%20from%20a%20second%20tab%20(%22Rate_Card%22).%20The%20formula%20snippet%20above%20is%20actually%20only%20one%20section%20of%2013...ergo%2C%20I%20have%2039%20arguments%20in%20just%20my%20original%20cell.%20That%20actually%20works%20fine%2C%20and%20I%20get%20one%20of%2013%20desired%20results%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20I%20need%20to%20modify%20this%20formula%20to%20also%20offer%20results%20with%20%22simple%22%20replaced%20with%20either%20%22moderate%22%20or%20%22complex%22%2C%20which%20then%20gives%20me%20roughly%20a%20page%20and%20a%20half%20of%20formulas%2C%20and%20an%20error%20that%20I%20have%20too%20many%20arguments.%20In%20other%20words%2C%20I%20can't%20triple%20the%20size.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20looked%20at%20various%20options%20online%2C%20and%20can't%20seem%20to%20find%20a%20viable%20alternative%20to%20the%20formula%20I'm%20using.%20Any%20ideas%20would%20be%20SO%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-841215%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-842100%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20advanced%20IF%2FAND%20nested%20formula%20(too%20many%20arguments)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404803%22%20target%3D%22_blank%22%3E%40vernp80%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20identify%20all%20possible%20combinations%20of%20B8%2C%20B18%2C%20and%20B35%2C%20along%20with%20your%20desired%20result%20for%20each%20combination.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842407%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20advanced%20IF%2FAND%20nested%20formula%20(too%20many%20arguments)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842407%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F404803%22%20target%3D%22_blank%22%3E%40vernp80%3C%2FA%3E%26nbsp%3B%2C%20if%20Rate_Card%20is%20organised%20as%20a%20matrix%20for%20all%20possible%20combination%20when%20INDEX%2FMATCH%20or%20like%20shall%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
vernp80
Occasional Visitor

Hi all,

I'm stuck with a fairly advanced (at least to me) nested IF/AND formula. I have a formula like this:

 

=IF(AND(B18="Onshore (US)",B8="Social Sciences",B35="Simple"),Rate_Card!E20

 

with the first three arguments coming out of my current tab, and the last coming from a second tab ("Rate_Card"). The formula snippet above is actually only one section of 13...ergo, I have 39 arguments in just my original cell. That actually works fine, and I get one of 13 desired results correctly.

 

My problem is that I need to modify this formula to also offer results with "simple" replaced with either "moderate" or "complex", which then gives me roughly a page and a half of formulas, and an error that I have too many arguments. In other words, I can't triple the size.

 

I've looked at various options online, and can't seem to find a viable alternative to the formula I'm using. Any ideas would be SO appreciated!

 

2 Replies

@vernp80 

Please identify all possible combinations of B8, B18, and B35, along with your desired result for each combination. 

@vernp80 , if Rate_Card is organised as a matrix for all possible combination when INDEX/MATCH or like shall work.

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