Argthtjtr

I'm not sure if the IF function is even the right one to start, but the question is:

I have a cell Y8 and it displays different numbers:

0,5,10,15,20,35,50

I need to type a formula that returns a text value, but I have 4 arguments.

I need it to return the following:

If it's...

• ...less then 10 = Level 1.


• ...between 10-19 = Level 2.


• ...20:29 = Level 3


• ...30+ = Level 4.

Using the IF function I only managed to get a return value of either Level 1 or Level 2 depending on the number which is constantly changing.

I have used the following formula:

=IF(Y8<=9, "Level 1", IF(Y8<="10:19", "Level 2")) 

Any ideas on what formula I can use to get a return value for all 4 arguments?

There are two ways you can do this depending on how thorough you want to be.

Personally I prefer the first example:

=IF(Y8<=9, "Level 1", IF(Y8<=19, "Level 2", IF(Y8<=29 "Level 3", "Level 4")))

You don't worry about defining Level 2 as being at least 10 as it is inferred that Y8 has to be greater than 9 by not matching and returning "Level 1".

If you wanted to really thorough and define upper and lower bounds then you could do something like this, it also includes returning a warning/error in case the value in Y8 is not a number:

=IF(Y8<=9, "Level 1", IF(AND(Y8>=10, Y8<=19), "Level 2", IF(AND(Y8>=20, Y8<=29), "Level 3", IF(Y8>=30, "Level 4", "Not a number or some other error"))))

You can use CHOOSE with MATCH

=CHOOSE(MATCH(Y8,{0,10,20,30}),"Level 1","Level 2","Level 3","Level 4")

If you are really wanting the Level and a number we can do:

="Level " & MATCH(Y8,{0,10,20,30})

Make a sorted lookup table with the Level corresponding to each threshold:

Value    Level

 0       1

10       2

20       3

30       4

Now you can use a simple VLOOKUP to get the Level given any Value, and if the thresholds ever need to change, or if new levels need to be added, your VLOOKUP formula doesn't need to change - just maintain the thresholds table, and done.

="Level " & VLOOKUP(theValue, theLookupTable, 2, TRUE)

Note the TRUE argument for the last parameter, making VLOOKUP use an approximate match rather than the typically-used exact match mode. As long as the thresholds are sorted ascending, VLOOKUP will return the Level value for the largest Value that is still smaller than the lookup value.

I like the answer by Scott Craner. However, I thought I would point out some other options.

If you have Excel 2016 or later, your can use the IFS function to simplify the structure of nested IF functions.

=IFS(Y8<10,"Level 1",Y8<20,"Level 2",Y8<30,"Level 3",TRUE,"Level 4")

The way it works is:

• If Y8 is less than 10, it will return "Level 1", otherwise ...


• If Y8 is less than 20, it will return "Level 2", otherwise ...


• If Y8 is less than 30, it will return "Level 3", otherwise ...


• It will return "Level 4"

In the example given in the question, the "level" number increases by one each time the value in Y8 increases by 10. In this special case, it is possible to calculate the level number without using IF.

="Level "&INT(MIN(30,MAX(0,Y8))/10)+1

Note that the question states that any value less than 10 is level 1 and any value greater than 30 is level 4. In order to force negative numbers to be "Level 1" and numbers greater than 39 to be level 4, the formula uses MIN(30,MAX(0,Y8)) instead of Y8.