Alteryx Vlookup with no consistent Primary Key

[RaptorZ]

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

ny

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.

[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?

[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.