 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 (25 = max).

The classification is as follows:

< 6 = Low

between 5 and 12 = Medium

> 12 = High

What I want Excel to do: Suppose in cell B2 there is the value 9, then in cell C2 there should be the result according to the classification. Result in C2 will then be: Medium.

Antother example: In cell B2 is the value 4, than in cell C2 Excel should be showing: Low.

The used formula (which is fault):

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

Row      Risk number (B)         Risk in words (C)

2                    12                                  High

3                     4                                   Low

4                     9                                   Medium

If above is not possible in Excel, please let me know. It would be much appreciated.

Sincerely,

Dominique de Jong

6 Replies

# Re: Multiple IF functions in one formula

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

Maybe like this. best response confirmed by Ddejong138 (New Contributor)
Solution

# Re: Multiple IF functions in one formula

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.

# Re: Multiple IF functions in one formula

Yes that worked. Many thanks for the help!

# Re: Multiple IF functions in one formula

Hi Hans, I think you are right about the classification. Well I am working as a quality assurance officer and a quality standard such as IFS (perhaps known) made the classification themself. I think not right as I may believe now.

So about the Excel. My thanks for the quick reply. I will try the above in my worksheet.