Forum Discussion
Bonus calculation % achieved, % payout
Hi - I am trying to set up a formula that would calculate bonus payout based on achievement. Lets say someone achieved 85% of their sales, their bonus is paid out at 25%. But the payout is scaled...
Hi Patrick, can you please help to understand how this formula works.
VLOOKUP(A2,{0,0;0.85,0.25;1,1;1.1,1.2},2,1)
I was using If Logical functions.
It's VLOOKUP with a multi-dimensional array as the table array.
The array is the same as placing this in the sheet:
VLOOKUP is looking for its lookup values within ranges with 1 (Approximate match). Any value from 0 to <.85 returns 0. Any value .85 or greater but less than 1 returns .25, etc.