if statement - Combine vlookup formulas into one formula with condition -

i'm trying combine 4 different array formula's. have tried suggestions on site didn't work.

here's couple tried:

arrayformula(if(and(k6>0,k6<=15),vlookup(d5,data!$f$2:$l$7,2,false),0))   arrayformula(if(and(k6>=16,k6<=99),vlookup(d5,data!$f$2:$l$7,3,false)))*k6 arrayformula(if(and(k6>=100,k6<=249),vlookup(d5,data!$f$2:$l$7,4,false)))*k6 arrayformula(if(and(k6>=250,k6<=499),vlookup(d5,data!$f$2:$l$7,5,false)))*k6 

hope can make formula.

first have mention using of arrayformula doing nothing in formulas. i've got rid of in solution.

i've made helper table make 1 formula:

then i've made 3 steps.

step 1. find column of vlookup

use vlookup sorted table find number of column return:

=vlookup(k6,$r$2:$t$5,2)

step 2. find multiplier

i see in first formula don't multiply *k6

but need 1 formula match conditions. use power of number:

1. k6 ^ 0 = 1
2. k6 ^ 1 = k6

to power used helper table again:

=vlookup(k6,$r$2:$t$5,3)

step 3. combine formula

the last thing done in 1 formula:

=if(and(k6>0,k6<=499),vlookup(d5,data!$f$2:$l$7,vlookup(k6,$r$2:$t$5,2),false),0) *k6^vlookup(k6,$r$2:$t$5,3)

i still use if-and in order prevent errors in vlookup part of formula.