Nested IF AND FORMULA for Tiered Fee Calculation

[slstewart249]

I am trying to write a nested IF AND Function to produce a result off of a Tiered Fee Table.
A . B . C . D
1 . Condition 1 Condition 2 Result if True . Result if False
2 . 1,000 2,000 1.7% . 0
3 . 2,000 3,000 1.6% . 0
4 . 3,000 . 4,000 . 1.5% . 0
5
6 . # to Be evaluated:
7 1,500
8 . 2,500

The below formula works for w having only two conditions (A2,B2).
If written it so =IF(AND(A7>=A2,A7<=B2),C2,D2)

How can I write a NESTED FORMULA to have multiple conditions ie. Row 2, Row 3, Row 4?

 

[arthurbr]

You could use differential rates with sumproduct

 

[Peter_SSs]

Does this do what you want?
If not & you still need help, please give more details about the exact result(s) want, where and why.

Excel Workbook
	A	B	C
2	1,000	2,000	1.70%
3	2,000	3,000	1.60%
4	3,000	4,000	1.50%
5
6
7	1500	1.70%
8	2,500	1.60%
Lookup

 

[Oscar Cronquist]

	A​	B​	C​	D​
1​	Condition 1	Condition 2	Result if True	. Result if False
2​	1,000​	2,000​	1.70%​	0​
3​	2,000​	3,000​	1.60%​	0​
4​	3,000​	4,000​	1.50%​	0​
5​
6​	6 . # to Be evaluated:
7​	1,500​	1.70%​
8​	2,500​	1.60%​

Sheet: Sheet43

Formula in cell B7:
=SUMPRODUCT((A7<$B$2:$B$4)*(A7>$A$2:$A$4)*$C$2:$C$4)