Forum Discussion

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
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.

HansVogelaar

MVP

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)

=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.

Ddejong138

Copper Contributor

Aug 18, 2022

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.