Vlookup - Retrieve corresponding value if amount falls within the range

Question

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       ProductTier 1Tier 1 Fee%Tier 2Tier 2 Fee%Tier 3Tier 3 Fee%Tier 4Tier 4 Fee%A  10,000,0001  20,000,0000.8  30,000,0000.7  B        10,0000.5        25,0000.45        50,0000.4        75,0000.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 IDProductAmountApplicable TierApplicable Fee2B        50,00130.43B        50,00020.454B        25,00010.56A  20,000,000118A  50,000,00030.7

Appreciate your advice.

SamDesk · Accepted Answer

Hi @Joon

So there are a few things to do here:

* First we need to clean and prep the data so it's in a format where we can join between the two data sources
* Then we need to apply the logic you've stipulated in determining the right value
* Finally (not shown here) you would want to reformat your currency values to hold thousand separators (commas) again.

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 🙂