Forum Discussion
IFS AND Function
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 SergeiBaklan ( i tagged you both because you gave really detailed answers on other issues)
4 Replies
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")))=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)
- 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?
The value in J4 in the screenshot should have been 340 instead of 240, but the formula should work.
See the attached sample workbook.
