Using VLOOK UP in ALTERYX

Hi,

I have a Input file where there are certain trades for which I need to apply Vlook up to fetch details from RAM file and give me output in result file. Please help me to apply Vlook-up for the trades which has comments as CAM & MDR (in last column). My objective is that I need to see the output as same as in Result file. Please help

Attaching the relevant files.

Input File.xlsx

RAM File.xlsx

Result.xlsx

Common Use Cases

Accepted answers

All comments

estherb47

Hi @HULK

You could Filter first on the Input File, to find the rows with the desired comments. A Join tool will effectively perform your VLOOKUP and bring the needed information together.

Let me know if that helps!

Cheers, Esther

Kenda

Hi @HULK

The join tool is perfect for this use case! Use your two input files as the inputs for the join tool and select the field from each that you want to use as the lookup field. Then you can use the select options in the configuration window to keep/exclude/rename the fields you want in your output. Hope this helps!

HULK

@Kenda @estherb47 - Many thanks for help. I will try using this. meantime will it be possible for you to build the same in ALTERYX workflow and share it here.

Kenda

@HULK try taking a look at that link I included in my last response. This one may be helpful, too: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Join/ta-p/29814

The sample workflow within Alteryx also provides a great example to learn from!

atcodedog05

Hi @HULK

I did look into your data and tried to arrive at the expected output sadly was a bit lost since I was not able to get the same result. Can you please share the keys which you are using for vlookup and an explanation of how you arrived at the expected output?

Also here is my addition, an interactive lesson on the find and replace tool which works like an excel vlookup (left full join and no duplicates) tool give it a look: https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201

Hope this helps : )

HULK

@atcodedog05 - In the input file, there is a last column named as COMMENT. So I need to apply VLOOK UP on column Trade ref (Column D) from INPUT FILE and Column as Trade ID (Column G) in RAM FILE for the line items where I have mentioned these comments as "CAM" & "MDR". So if you check the RESULT file there are only 3 items

@atcodedog05 - please advise on the above, thanks a lot

Kenda

@HULK

A Join tool is perfect here. If you only want records from your Input file that have the comments field populated, first use a filter to include only these records. Next, connect this data to the L input on a Join tool and your RAM file to the R input on that join tool. In the configuration, select the Trade ref field from the L Input and the Trade ID from the R input dropdown. This should get you the output you're looking for.

Luke_C

Hi @HULK Use a join tool and jone Trade ID & Trade Ref. Because you have duplicates in the input file, you may want to summarize first before joining to avoid a cross join. There are plenty of great resources linked above.

Give it a go and attach your workflow if it's not working - better than having your work done for you.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703