Hi
I have a big fee schedule spanning over multiple products. The maximum tiers differs depending on products. This is what the existing table looks like
Table 1 - Fee Table | ||||||||
Product | Tier 1 | Tier 1 Fee% | Tier 2 | Tier 2 Fee% | Tier 3 | Tier 3 Fee% | Tier 4 | Tier 4 Fee% |
A | 10,000,000 | 1 | 20,000,000 | 0.8 | 30,000,000 | 0.7 | ||
B | 10,000 | 0.5 | 25,000 | 0.45 | 50,000 | 0.4 | 75,000 | 0.35 |
The way to read the above table is
E.g for product B
Over 10k to <= 25k : 0.5%
Over 25k to <= 50k : 0.45%
..
over 75k : 0.35%
I am trying to retrieve the corresponding tier and fee based on the size of the amount to the following table (i.e. Applicable Tier, Applicable Fee)
Table 2 - Data | ||||
Case ID | Product | Amount | Applicable Tier | Applicable Fee |
2 | B | 50,001 | 3 | 0.4 |
3 | B | 50,000 | 2 | 0.45 |
4 | B | 25,000 | 1 | 0.5 |
6 | A | 20,000,000 | 1 | 1 |
8 | A | 50,000,000 | 3 | 0.7 |
Appreciate your advice.
Solved! Go to Solution.
Hi @Joon
So there are a few things to do here:
There are a few ways to perform the 'VLookup', it really depends on the volume of your data. An alternative could be to generate a value in the Tier Table for every whole currency number.
Hope this helps!
Sam 🙂
Thanks Sam! Your proposed solution helps. I was trying to avoid the other option of generating a value in the Tier Table for every number as I don't think it is very efficient given the long list of products and wide range of amounts.