Forum Discussion

Laura Lane's avatar
Laura Lane
Copper Contributor
Dec 18, 2017

Nested IF Statement?

=IF(D2<273,”Low Level 1”,IF(D2<289,”Middle Level 1”,IF(D2<304,”High Level 1”,IF(D2<313,”Low Level 2”,IF(D2<321,”High Level 2”,IF(D2<336,”Level 3”,IF(D2<352,”Level 4”,”Level 5”))

 

Someone please take a look at this. It's not running. I've taken out all spaces etc. 

Formulas & Functions
Need Help

2 Replies

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    Dec 18, 2017

    Hello,

    as an alternative to nested IFs, which can get very unwieldy, I suggest you create a lookup table with the threshold numbers in one column and the level text in the next column. Then you can use a  simple Vlookup formula to return the result.  A lookup table is much easier to maintain than the nested IF formula.

     

    =VLOOKUP(D2,$H$2:$I$9,2,TRUE)

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Dec 18, 2017

    Laura,

     

    At the end of the formula, you must type parentheses according to the number of IF functions in the formula, in order to close each function.

     

    This is the correct syntax:

    =IF(D2<273,"Low Level 1",
  IF(D2<289,"Middle Level 1",
  IF(D2<304,"High Level 1",
  IF(D2<313,"Low Level 2",
  IF(D2<321,"High Level 2",
  IF(D2<336,"Level 3",
  IF(D2<352,"Level 4","Level 5")))))))

Resources