Help with an IF formula returning multiple values

[ajpclifford19]

Hello all thanks for any help I get.

Here is the scenario

I want cell b2 to display a text based on the input of b1.


If b1= (any number between 1-6 display Low, if it displays any number between 8-10 display medium, if it displays any number between 12-25 display high )

I've simplified it but here is the syntax i currently have:
=IF(ISNUMBER(SEARCH("1",Table2[Risk Number])),"Low",IF(ISNUMBER(SEARCH("2",Table2[Risk Number])),"Low",IF(ISNUMBER(SEARCH("3",Table2[Risk Number])),"Low",IF(ISNUMBER(SEARCH("4",Table2[Risk Number])),"Low",IF(ISNUMBER(SEARCH("5",Table2[Risk Number])),"Low",IF(ISNUMBER(SEARCH("6",Table2[Risk Number])),"Low",IF(ISNUMBER(SEARCH("8",Table2[Risk Number])),"Moderate",IF(ISNUMBER(SEARCH("9",Table2[Risk Number])),"Moderate",IF(ISNUMBER(SEARCH("10",Table2[Risk Number])),"Moderate",IF(ISNUMBER(SEARCH("12",Table2[Risk Number])),"High",IF(ISNUMBER(SEARCH("15",Table2[Risk Number])),"High",IF(ISNUMBER(SEARCH("16",Table2[Risk Number])),"Extreme",IF(ISNUMBER(SEARCH("20",Table2[Risk Number])),"Extreme",IF(ISNUMBER(SEARCH("25",Table2[Risk Number])),"Extreme",""))))))))))))))

The problem is anything past 9 displays Low. I.e 10-19 reads as 1, 20-29 reads as 2. How do I resolve this?

Thanks!

[Olijan29]

=IF(F2<7,"LOW",(IF(F2<11,"medium",(IF(F2<26,"high

Try this idea although I forgot about extreme but you get the idea

[ajpclifford19]

That will work just fine.

Thank you!.

[Olijan29]

Your welcome

[FDibbins]

Another way to do this, which will give you more flexibility (if you decide to change the ranges, you wont need to change the formulas), would be to create a small table (see below) and then use a vlookup. Something like this...

	D	E
9	20	Extreme
10	0	Low
11	7	Moderate
12	11	High
13	16	Extreme

E9=VLOOKUP(D9,$D$10:$E$13,2,1)
This is what you would put in column G