Alteryx Vlookup with no consistent Primary Key

I have 2 files.

One file has every field filled in

the other file has sporadic data, in the sporadic data file I have created 8 fields that are blank at the end of it.

I want to take my sporadic file and do a vlookup to the file with all the data and then if it exists anywhere in a specific field bring back that field and append it to the blank field. So visually.

FILE 1:

CITY

Chicago

indy

FILE2:

CITY

amherst

pensacola

chicago

also on file2 a blank field called CITYMATCH

if the city in file 2 is found in file 1 then i want file 1's value to be updated on that line in the CITYMATCH field.

this would occur 7 more times for 7 other fields....each appending to their own "MATCH" field.

In the end I would have in FILE 2, blanks in the respective fields where no match but values from file one in that field if there was a match.

In file 2 this would look like this.

CITY CITYMATCH

amherst

pensacola

chicago chicago

again there would be 7 additional "MATCH" fields with the same concept

thanks for any thoughts, it's simple to do in excel, just can't figure out what tool to use in alteryx.

Help

Accepted answers

All comments

OTrieger

@RaptorZ
You will be able to achieve getting it with a Batch Macro, Control Parameter will be the items from 2nd file.

ChrisTX

Can you post sample input and expected output in table format or in an XLSX attachment?

The question is difficult to follow.

RaptorZ

a Macro for a vlook up...ok, haven't used those much at all. Ill learn about it and if it's the solution i'll put it here!

thanks!

CoG

The concept of vlookup can be replicated with Find and Replace Tool (w/ appending fields) or the Join Tool in addition to Union Tool. Batch macro would be used to accomplish this for multiple columns.

RaptorZ

Find and replace wil not work b/c I am creating a new field to put these values in, I tried that. I even tried to create the fields first and use them to replace to, and the F&R tool would not put those newly created blank fields as an option to output the match to

admittedly I am newer to alteryx as well, so this may be something simple, it would take all of 2 minutes to do this in excel, perhaps i'll just write sql code rather than use a tool, b/c this seems a bit more complicated than it should be

CoG

Did you select to Append records in the Find and Replace Tool?

Sample Workflow.yxmd

apathetichell

so that we are clear - vlookup is not a thing. It's an Excel invention which exsists ONLY IN EXCEL. having said that. recordid use transpose on your potential match columns with recordid as a key field. set your case to one specific case ie uppercase or lower case. do the same with your match terms. use a join or a find/replace. see the results. do whatever you need.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703