Multiple Vlookups

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

Alteryx Community.xlsx

Join

Accepted answers

seinchyiwoo

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

for white summer.yxzp

All comments

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-VLOOKUP-in-Alteryx/ta-p/37953

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

seinchyiwoo

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

for white summer.yxzp

whitesummer

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335