Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Vlookup - Retrieve corresponding value if amount falls within the range

7 - Meteor



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 Fee
2B        50,00130.4
3B        50,00020.45
4B        25,00010.5
6A  20,000,00011
8A  50,000,00030.7


Appreciate your advice.

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.


Annotation 2020-08-26 153418.png


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.