Forum Discussion

sbennett2345's avatar
sbennett2345
Copper Contributor
Aug 22, 2025
Solved

Formula help - not sure where to start

I have this data:    I want to create a sort of calculator where based on the rate and production multiplier, the result is the commission structure value. The problem is, the calculation I am ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Aug 22, 2025

    Let's say the rate 0.14 is in J1 and the production multiplier 1.32 is in J2.

    The corresponding commission structure is

    =LET(Dif, ABS(J2-FILTER($F$2:$F$12, $E$2:$E$12=J1)), 
     MinDif, MIN(Dif), 
     Com, FILTER($G$2:$G$12, $E$2:$E$12=J1), 
     XLOOKUP(MinDif, Dif, Com))

    You can adjust the ranges of course.

     

flexyourdata's avatar
flexyourdata
Iron Contributor
Aug 23, 2025

Here's an option that doesn't use FILTER. 

=TAKE(SORT(ABS(A2:C12-HSTACK(F2,F1,0)),{1,2}),1,-1)

 

Resources