Assigning rate % and Vlookup in tier calculation

[DiyaR]

Hi,

I am trying to figure out Rate column based on the tier table below..  I was trying to do with Append/Join along with formula but failing as it gives me multiple row output. I am sure there is a way but I am not thinking in right direction or new way of doing it for tier calc along with V lookup for Rule 

Tier table

Rule	From	To	Rate
A2	-	50,000.00	1.35%
A2	50,001.00	100,000.00	0.90%
A2	100,001.00	200,000.00	0.50%
A2	200,001.00	500,000.00	0.40%
A2	500,001.00	1,000,000.00	0.10%
C3	-	100,000.00	1.15%
C3	100,001.00	250,000.00	1.00%
C3	250,001.00	1,000,000.00	0.35%

Claim table

Acct	Claimed	Rule	Rate should be
Bell	60,000.00	A2	My first 50,000 should be @ 1.35% and 10,000 @ 90%
Bell	80,000.00	A2	0.90%
Joe	252,000.00	C3	it will be combination of 1.15% and 1%

Thank you and much appreciated 🙂

[ImadZidan]

Hello @DiyaR ,

I had to clean up the data manually for now.

this will give you an idea.

hope it helps

[Greg_Murray · Accepted solution]

@DiyaR,

If I understand the question, you just need a join and a couple of filters to figure out if the claimed amount falls within the tiers. From there you can us a formula to figure out what portion falls within each tier. 

[DiyaR]

Sorry, I was not great in explaining... But yes, I wanted the claim to fall in the tier. Thank you and much appreciated 🙂

[DiyaR]

Thank you.. I was doing the same thing, however my claim was not falling in the tier.