Formula to calculate tiered commission based on changing %

[ITWORX]

Hi, pls can i ask for assistance with below formula. I have attached screenshot on actual sheet.

=IF(B1>A3,B1*C3,IF(B1>A4,B1*C4,IF(B1>A5,B1*C5,IF(B1>A6,B1*C6,IF(B1>A7,B1*C7,IF(B1>A8,B1*C8))))))

Basically, when the value in yellow exceeds any amount in C3 - C8, i was hoping the formula would choose the corresponding rate in C3 - C8, however it seems stuck on only using 2.5% ( C3 ) in the output cell ( D3 ). I ideally would like it to move through the rate 2.5%, 5% etc as value in B1 exceeds values in a3 - a8. Hope makes sense

 

[Fluff]

How about

=XLOOKUP(B1,A2:A8,C2:C8,0,-1)*B1

but you will need to add a row for 0, otherwise you'll get an error.

 

[ITWORX]

works 100%, thanks you

 

[Fluff]

You're welcome & thanks for the feedback.