Excel formula for greater than but less than with several tiers

Question

I have a few hundred rows of data, and each has a number between 1 and 200, and I'd like to put them in categories of 1-5 depending on where that number is.

The categories look like this:

---

Zones   Min  Max
1       0    35
2       35   60
3       60   85
4       85   110
5       110  200

---

I want to assign it a Zone if it is greater than the Min, but less than the Max.

I have 2 formulas I've been working with to solve it. One is a nested IF AND statement:

=IF(A1<=35,1,IF(AND(A1<=60,A1>35),2,IF(AND(A1<=85,A1>60),3,IF(AND(A1<=110,A1>85),4,IF(AND(A1<=200,A1>110),2,"TOO BIG")))))

The 2nd formula attempts to use a SUMPRODUCT function:

=INDEX($C$2:$C$6,SUMPRODUCT(--(A1<=$E$2:$E$6),-- (A1>$D2:$D$6),ROW($2:$6)))

Rather than have to continue to adjust the numeric values manually, I set them as absolutes, which is why this formula is slightly different. The E column is the Max value set, and the D is the Min value set.

Any help would be appreciated!

Answer 1

Use this:

=MATCH(A1,{0,35,60,85,110})