alteryx Community

# Alteryx Designer Discussions

SOLVED

## Vlookup - Retrieve corresponding value if amount falls within the range

7 - Meteor

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

2 REPLIES 2
11 - Bolide

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 🙂

7 - Meteor

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.

Labels