Forum Discussion

Ddejong138's avatar
Ddejong138
Copper Contributor
Aug 18, 2022
Solved

Multiple IF functions in one formula

Hi, I am new here and I have a problem with a project regarding a formula. Excel is saying #Value, which off course is not a good sign.   I have a list with values below each other from: 1 to 25 (2...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Aug 18, 2022

    Ddejong138 

    You mentioned "between 5 and 12" = medium and ">12" = high. So 12 should be medium.

    (Also, I think it should be "between 6 and 12")

    You can use

    =LOOKUP(B2;{1\6\13};{"Low"\"Medium"\"High"})

    or create a lookup list:

    And use either

    =VLOOKUP(B2;$G$2:$H$4;2)

    or

    =XLOOKUP(B2;$G$2:$G$4;$H$2:$H$4;"")

    The advantage of the lookup list is that it's easy to change the thresholds - you have to do it only in one place instead of in each formula.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 18, 2022

Ddejong138 

=IF(AND(M4>=5,M4<=12),"Medium",IF(M4<=6,"Low",IF(M4>=12,"High")))

Maybe like this.

 

  • Ddejong138's avatar
    Ddejong138
    Copper Contributor
    Aug 18, 2022
    Yes that worked. Many thanks for the help!

Resources