Vlookup - Retrieve corresponding value if amount falls within the range

[Joon]

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.

[SamDesk · Accepted solution]

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 🙂

[Joon]

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.