Multiple Vlookups

[whitesummer]

Hello,

I'm trying to figure out how I can use similar Vlookup function to find the Allowance % based on customer name and Part  number

For example:

I want the final result to look like this with column D allowance % populated below:

Based on the input below:

As you can see that because customer AAFES and Amazon have product code "all"-col C, so it means all of their products will take whatever total allowance % available: zero and 0.2% respectively

But customer Mckesson Drug, will have multiple products (see product code column C), so some of it will take different allowance% and the rest (if can't find that product code will be zero%)

Not sure if I explain it clearly, but I really appreciate your help!

Thank you.

Please see the attached input and output

[seven]

Hi @whitesummer 

use a Filter tool on Product Code to split the customers who have "All" from those that have a part number. For those with "All", do a Join to get Allowance on just Customer. For those with a Product Code, do a join on Customer and Product Code. That's how you'll achieve the multiple vlookup functionality you want. Futhermore, if there is no product code, then those records will not come out of the J output of the Join. They will come out of the L or R output, depending on how you set up the Join. Add a Formula tool to that side, create a formula for Allowance, make it 0, and then Union that output to the J output. Please reference these posts for more information on how to do a vlookup in Alteryx and how to do a left join.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-for-Excel-Users-How-to-do-a...

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Left-Right-and-Full-Outer-Joins-are...

[seinchyiwoo · Accepted solution]

Hey,

A quick workflow using Join and Union.

Whenever you think about merging multiple datasets ie Vlookup - it will be join. Similar to the overall concept that @seven has proposed, using L/R node allows you to identify rows that don't match.

Use that to tweak your linking field further.

Hope this helps.

-Seinchyi

[whitesummer]

Yay! Thank you so much @seven and @seinchyiwoo. I really appreciate it! It works 😉

[seven]

Hi,

you still need to add a Formula tool! Per the original requirement "and the rest (if can't find that product code will be zero%)", the way these joins are set up, the allowance % will be NULL, not 0. That's why you need to add the Formula tool to make the unjoined records allowance % equal to 0. If my solution works for you, please accept it. Thank you.