IF statement issue appears to be only reading 2 digits

[MarkintheHV]

I need to match criteria on the column U, and have written a Boolean if statement (see below)to match on the desired ranges and place the appropriate value in column X which would be 1-8.  As you can see below, the formula works fine on rows 1,2,3 and when it hits row 4, the formula breaks, then adds the value of 1 on the remaining columns which should be 6,7,8.  I suspect that the if statement for some reason is only looking at the first 2 digits of column U because row 4-9 is where the 3 digit ranges start, and I get a failure due to the formula being =>96 and <128, which I suspect Excel is reading =>96 and <12 which generates the value of false and if it is indeed only reading the first 2 digits that would explain the values of 1 for 160, 192, 224, 255.

=

IF(AND(U2 >= "0",U2 < "32"),1,

IF(AND(U2 >= "32",U2 < "64"),2,

IF(AND(U2 >= "64",U2 < "96"),3,

IF(AND(U2 >= "96",U2 < "128"),4,

IF(AND(U2 >= "128",U2 < "160"),5,

IF(AND(U2 >= "160",U2 < "192"),6,

IF(AND(U2 >= "192",U2 < "224"),7,

IF(AND(U2 >= "224",U2 < "256"),8

))))))))

Test data is attached.

Any ideas as to how to solve this?

[#2]

@MarkintheHV 

The problem is in the workbook, not in the picture,

[#3]

@MarkintheHV 

I guess you work with numbers, if so correct one will be

IF(AND(U2 >= 0,U2 < 32),1,
IF(AND(U2 >= 32,U2 < 64),2,
IF(AND(U2 >= 64,U2 < 96),3,
IF(AND(U2 >= 96,U2 < 128),4,
IF(AND(U2 >= 128,U2 < 160),5,
IF(AND(U2 >= 160,U2 < 192),6,
IF(AND(U2 >= 192,U2 < 224),7,
IF(AND(U2 >= 224,U2 < 256),8
))))))))

or

=LOOKUP(U2,
       {0,32,64,96,128,160,192,224,256},
       {1,2,3,4,5,6,7,8,0})

[#4]

Interestingly when I remove the quotes as illustrated above, all the values return as false. I have verified the formatting of the cells and they are all formatted as number

[#5]

Check your numbers in column U, make sure there aren't spaces with Char(32), or Char(160).
Char(32) can be removed by the TRIM Function
Char(160) can be removed by the SUBSTITUTE Function

=TRIM(SUBSTITUTE(U2,CHAR(160),))

Cheers

[#6]

@MarkintheHV 

I see, that's since U2 is also the text, you need to convert it number to have correct results. That could be

=IF(AND(--U2 >= 0,--U2 < 32),1,
IF(AND(--U2 >= 32,--U2 < 64),2,
IF(AND(--U2 >= 64,--U2 < 96),3,
IF(AND(--U2 >= 96,--U2 < 128),4,
IF(AND(--U2 >= 128,--U2 < 160),5,
IF(AND(--U2 >= 160,--U2 < 192),6,
IF(AND(--U2 >= 192,--U2 < 224),7,
IF(AND(--U2 >= 224,--U2 < 256),8
))))))))

or

=LOOKUP(--U2,
       {0,32,64,96,128,160,192,224,256},
       {1,2,3,4,5,6,7,8,0})