Return different values depending on if a value is between numbers in Google Sheets-CodePudding

I have a value, let's say 138. I need a way to check what % it corresponds to. In this case 138 is between 135 and 164 so it must return 34%.

If value is less than 75 return 0%. If value is more than 285 return max which is 60%.

Everything is in the same Google Spreadsheet. The table with % is in a different sheet named RENDIMENT. To look for values in that sheet I use =RENDIMENT!X7

Table with % depending on minium and maxium value

CodePudding user response：

I finally have it. I ended up with this code:

=IFS(E5<RENDIMENT!$X$7;0%; C5=true;RENDIMENT!$W$16; AND(E5>=RENDIMENT!$X$7;E5<=RENDIMENT!$Y$7);RENDIMENT!$W$7; AND(E5>=RENDIMENT!$X$8;E5<=RENDIMENT!$Y$8);RENDIMENT!$W$8; AND(E5>=RENDIMENT!$X$9;E5<=RENDIMENT!$Y$9);RENDIMENT!$W$9; AND(E5>=RENDIMENT!$X$10;E5<=RENDIMENT!$Y$10);RENDIMENT!$W$10; AND(E5>=RENDIMENT!$X$11;E5<=RENDIMENT!$Y$11);RENDIMENT!$W$11; AND(E5>=RENDIMENT!$X$12;E5<=RENDIMENT!$Y$12);RENDIMENT!$W$12; AND(E5>=RENDIMENT!$X$13;E5<=RENDIMENT!$Y$13);RENDIMENT!$W$13; AND(E5>=RENDIMENT!$X$14;E5<=RENDIMENT!$Y$14);RENDIMENT!$W$14; E5>RENDIMENT!$X$15;RENDIMENT!W15)

CodePudding user response：

lets say your value is in A1, use:

=IFNA(VLOOKUP(A1, {0, 0%; RENDIMENT!X7:X, RENDIMENT!W7:W}, 2, 1))

or:

=IFNA(VLOOKUP(A1; {0\ 0%; RENDIMENT!X7:X\ RENDIMENT!W7:W}; 2; 1))