Formula results outputting the wrong numbers for range-CodePudding

My formula is not outputting the proper result.

Weights that are between 5 and 8 on column I should output as 10 on column J. However, if you look at 7.30 weight, it is outputting as 15. 6.68 shows as 15. 6.88 as 15 and etc. Some of them are correct but the other half is wrong.

Is there something wrong with the formula?

=INDEX({7,10,15,30,55,70,80,100,110},MATCH(1*I1,{0,4,8,15,25,35,45,55,65,70}))

CodePudding user response：

Clear I2:I and try this in I2:

=ArrayFormula(IF(I2:I="",,VLOOKUP(1*I2:I,{{0;4;8;15;25;35;45;55;65},{7;10;15;30;55;70;80;100;110}},2,TRUE)))

In your original formula, for starters, you have 9 elements in one array and 10 in the other.

However, I find VLOOKUP easier to manage.

CodePudding user response：

try in J2:

=INDEX(IFNA(VLOOKUP(I2:I, 
 {{0;  4;  8; 15; 25; 35; 45;  55;  65},
  {7; 10; 15; 30; 55; 70; 80; 100; 110}}, 2, 1)))