CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Cornelius Wilson
Cornelius Wilson

Reputation: 2914

Formula results outputting the wrong numbers for range

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.

enter image description here

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}))

Upvotes: 0

Views: 43

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

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.

Upvotes: 2

player0
player0

Reputation: 1

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)))

Upvotes: 0

Related Questions