Vlookup lies between

[rohini]

Hi All,

In my data I have a days column which ranges from 0 - 800, based on this column I want to add a age of the bill column which will give me like if its 0 to 41  days the it would be  6 weeks or less and further. In excel i did this by vlookup. In alteryx sould I give this as manual input or an if formula.

As I tried the manual input it only shows for 0 - 6 weeks, for 42 - 6weeks.

0	6 weeks or less
42	6 weeks - 2 years
730	2 years +

Thank you

Regards

Rohini

[Kenda]

Hi @rohini 

I would suggest creating a new field with a Formula tool with the following expression:

iif([Days]<42, "6 weeks or less", 
     iif([Days]<720, "6 weeks - 2 years", "2 years +"))

This first checks if the Days field is less than 42. If so, the new field will say 6 weeks or less. If not, it will next check if the Days field is less than 720. If so, the new field will say 6 weeks - 2 years. If neither of those conditions are true, the new field will say 2 years +.

Hope this helps!

[estherb47]

Hi @rohini 

Great idea, @Kenda   You could also use an append tool to append all of the possible lookups, and a filter to find where they lie in the lookup table. 

and finally, you could use a generate rows tool to find all of inbetween values, and a join tool to match. 

let me know if that helps. 

cheers!

esther