IFS AND Function

[Ade_On360]

Hey so I'm trying to create a column where depending on the amount of points a column would display

Level  1, Level  2, Level  3

The point are distributed

Level 1 (0 - 219)

Level 2 (220 - 339)

Level 3 (340 - 480)

I've tried using =IF(AND(C2>=1,C2<-219"Level 1") but it's not executing.

Please help

@amit_bhola    @Sergei Baklan ( i tagged you both because you gave really detailed answers on other issues)

[#2]

@Ade_On360 

=LOOKUP(C2,{0,220,340},{"Level 1","Level 2","Level 3"})

Or create a lookup table:

and use =VLOOKUP(C2,$J$2:$K$4,2)

 

[#3]

Hey ive tried using the lookup table but it gives an error message. maybe cause it needs to be within the ranges and not just a match?

[#4]

@Ade_On360 

The value in J4 in the screenshot should have been 340 instead of 240, but the formula should work.

See the attached sample workbook.

[#5]

@Ade_On360 Hi, just for the academic purpose, if it is to be done using IF and AND, then the solution is as below/attached. Take care to close the bracket for AND function within IF function. When a function is used within another function, its bracket needs to be closed each time.

And yes, the lookup table method using VLOOKUP is recommended instead of IF function for no. of levels beyond two. Lookup method is easy to understand and it is also easy to manage the change of level defining numbers by just typing the new values into the lookup table cells.

=IF(AND(C2>=0,C2<220),"Level 1",IF(AND(C2>=220,C2<340),"Level2",IF(AND(C2>=340,C2<=480),"Level3","Level not defined")))