I am trying to construct a pay table.

There are 12 pay ranks, each of which have associated Bonus and Vacation rates.

I am using VLOOKUP with the variable pay rank. The problem lies that if at a Pay rank of “10” found in A11 which VLOOKUP gives a Bonus of “8” I also need the outcome to add up the Bonus for Pay ranks 1-9.

Something like =SUM(D2:VLOOKUP(G7,A2:E12,4,0)) conceptually looks right, a range from D2 to wherever the end of that is (D11 in this case) though, clearly that didn’t work.

I hope I’ve explained this well enough.