VLOOKUP to Find the Closest Match with numeric value

Question

Hi..

I have a lot of the "True" Vlookup use cases in my excel to be recreated in Alteryx and so far I haven't had a good solution without writing a long nesting IF stagement.  Sample case as below to calculate bonus:

Bonus % TableSalesIncentive % $               -  0% $      20,0015% $      40,0018% $      50,00110%

And I have to get the bonus rate with each sales' sales number:

EmpSalesBonus RateA $  35,9615%B $  14,4730%C $  42,6568%D $  79,20010%

With excel it's very easily done with either vlookup or xlookup -- xlookup can even let you pick -1 or 1 so the it's even more versatile for a lot of use cases (I do have one use case to match with the next "larger" number/range).

Since the lookup value is numeric not string, unfortunately somehow I didn't seem to be able to find a good solution to make it work (find/ replace, join, union..).  Also since my real application is way more complicated and both the "lookup_value" and "return_value" change quite often.  The long IF statement like "If sales < 20000, then 0%, if sales < 40000, then.." really isn't a good solution to me.

Anyone can give me some pointers?

Thanks a million!

Maskell_Rascal · Accepted Answer

Hi @goutdelete

Instead of using a formula logic, you can first append the bonus table to the list of employee sales. Then filter down to where the bonus sales tier is less than the employee's sales, and finally use a sample tool to grab the last record for each employee.

Attached is a workflow I mocked up based on your screenshots. Let me know if this works for you.

Cheers!

Phil

Jonny · Accepted Answer

Hi @goutdelete ,

I have tried my hand at it, see if this is what you are looking for.

cheers!

BR/Jonny