vlookup with multiple values

[Chauche]

Hi Experts,

I am looking up for a way to VLOOKUP raw file data with Master file data and concatenate same values in raw as result based on short or long Identifier available in Master file. Any quick help is appreciated.

Master file data

Short Id	Long id
abc	US123
pqr	IN123
pqr	IN123
xyz	RU345
abc	US123
rst	AU456

Raw file (have mix of Master file short & Long identifier)

Values	Identifier
1cyv34	abc
tyvn341	pqr
ghiet12	pqr
450njye	RU345
gui534	abc
nbde890	AU456

Result in raw file required

Values	Identifier	Result
1cyv34	abc	1cyv34; gui534
tyvn341	pqr	tyvn341; ghiet12
ghiet12	pqr	tyvn341; ghiet12
450njye	RU345	450njye
gui534	abc	1cyv34; gui534
nbde890	AU456	nbde890

[taylor_butler · Accepted solution]

Hi @Chauche , 

There are likely a number of different ways to get to your result. I've attached mine below for your reference. To summarize, I used the Find & Replace tool to append the Identifier from the Master File if the ID is found in the Raw Data file & then concatenated Values that have the same ID. From there I matched the data back up to the raw data file. 

Let me know if you have any questions! Interested to see if anyone is able to achieve this in fewer steps.

[flying008]

Hi, @Chauche 

the other way for you :

[Chauche]

Hi @taylor_butler

Thanks for the solution. I can see you have find & replace the short id but not the Long Id which I did taking help from your solution.

Thanks again.