Forum Discussion

vernp80's avatar
vernp80
Copper Contributor
Sep 06, 2019

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

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

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

  • Twifoo's avatar
    Twifoo
    Silver Contributor

    vernp80 

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