Formula with reference and "if"

New Contributor

I have a pretty simple sheet, which I would like to add a formula to, but I cannot find the right formula. 

 

Please see the attached test-sheet.

 

I would like the sheet to automatically give a discount on the customers order, which I fill manually. The discount gets higher, after how much they order. But the discount also gets higher if they make a longer commitment, fx two or three year deals. 

 

I therefore have 9 automated discounts (see the box on the right)

 

I would like the box in the bottom to automatically select which discount to give. 

 

The logical explanation for fx one year would be:

 

If the total amount is less than 50.000, choose discount 1, and if the total amount is between 50.000 and 100.000, choose discount 2, and if the total amount is above 100.000 choose discount 3.

 

But I can't seem to get it right. HELP PLEASE

 

 

 

3 Replies

@Beginner1075

In B19:

 

=$D$16*(100%-INDEX($G$5:$I$7,ROW($A19)-ROW($A$19)+1,MATCH($D$16,{0,50000,100000})))

 

Fill down to B21.

check this and i hope it will helps you@Beginner1075 

@alexanderjaml Great, it worked!! Thank you so much for your help.