IF function - multiple options for cell

[Natalieb95]

Hello,

In the attached Excel doc I have highlighted the cells in yellow that I am looking at. This is for working out commissions off of sales. The rule is:
If a sales consultant's total retail value is:
less than $200 = 0
$200-$300 = multiplied by 15%
$300-$400 = multiplied by 25%
$400 + = multiplied by 40%

I need the one cell (e.g AA8 to be able to have all those rules applied). Then I will be able to drag the formula down for the rest of the rows.

Thanks!

[#2]

@Natalieb95 

Create a helper table with the commission data.

=Z8*XLOOKUP(Z8,tbl_Commission[Lower boundary],tbl_Commission[%],,-1,1)