Calculating amount * % rate when referencing % based on range from another table

[Gunnerrecall1776]

Essentially trying to figure out a formula or what function to use to figure how much what % will come out of a base amount, where the % rates are referenced from a separate table displaying value ranges and what % rates apply to them.

https://imgur.com/gallery/tYKuK

Its a pretty basic example of what im trying to do, if i can get help figuring out how start that I can tune up any tweaks I have to do to get It to work i think. Any help would be appreciated, and thank whoever in advance.

 

[cunningAce]

This formula should do it

=IF(AND(B1>=A7,B1<=C7),D7,IF(AND(B1>=A8,B1<=C8),D8,IF(AND(B1>=A9,B1<=C9),D9)))

 

[JumpingCrab]

You can also use an array formula, which will make it a bit easier to work with longer tables of value ranges.
Based on the layout in your picture, the formula in B2 would be:

{=SUM(($B$1>=A7:A9)*($B$1<=C7:C9)*(D7:D9))*$B$1}

You have to hit CTRL-SHIFT-ENTER (not just ENTER) when entering to change the formula in an array formula.
If you need a longer value range, just change the ranges A7:A9, etc...

Hope this helps.
JL

 

[JumpingCrab]

Just realized you can also use sumproduct:

=SUMPRODUCT(($B$1>=A7:A9)*($B$1<=C7:C9)*(D7:D9))*$B$1

That does not require an array formula.

 

[AhoyNC]

Another option if you have a table then maybe VLOOKUP:

Excel Workbook
	A	B	C	D	E
1	Amount	2300
2		115
3
4
5
6	Table
7	0	to	999	2%
8	1000	to	1999	3%
9	2000	to	2999	5%
Sheet