How to concatenate using a Lookup Table

[hcho]

I want to concatenate the fields where the fields are specified within a lookup table.

I'm referring to an existing post, but I'm facing challenges handling multiple field names separated by commas within a single cell.

The workflow I'm aiming to create should accept two inputs:

1. Data:

RecordID	Industry	FirstName	LastName	Age	YearsOfEducation	YearsAtCurrentJob	Salary
1	Communication	Ronald	Dryer	43	16	5	83000
2	Manufacturing	Angelina	Prissant	33	18	4	87000
3	Manufacturing	Tara	Jackson	28	12	6	35000
4	Communication	Keith	Jacobs	56	14	8	63000
5	Manufacturing	Terrence	McKinley	26	18	2	80000
6	Manufacturing	Wilma	Holmes	63	16	12	77000
7	Communication	Mark	Hodges	46	12	2	34500
8	Software	Danielle	Rodriguez	29	22	1	108000
9	Software	Michael	Lawrence	53	16	7	93000
10	Software	Michael	Lawrence	53	16	7	930
11	Software	Christina	Kim	32	16	4	88000
12	Software	Christina	Li	32	16	4	88000

2. Lookup Table

FileName	Field	Type
Customer	FirstName,LastName	Unique

Desired output:

RecordID	Industry	FirstName	LastName	Age	YearsOfEducation	YearsAtCurrentJob	Salary	FullName
1	Communication	Ronald	Dryer	43	16	5	83000	RonaldDryer
2	Manufacturing	Angelina	Prissant	33	18	4	87000	AngelinaPrissant
3	Manufacturing	Tara	Jackson	28	12	6	35000	TaraJackson
4	Communication	Keith	Jacobs	56	14	8	63000	KeithJacobs
5	Manufacturing	Terrence	McKinley	26	18	2	80000	TerrenceMcKinley
6	Manufacturing	Wilma	Holmes	63	16	12	77000	WilmaHolmes
7	Communication	Mark	Hodges	46	12	2	34500	MarkHodges
8	Software	Danielle	Rodriguez	29	22	1	108000	DanielleRodriguez
9	Software	Michael	Lawrence	53	16	7	93000	MichaelLawrence
10	Software	Michael	Lawrence	53	16	7	930	MichaelLawrence
11	Software	Christina	Kim	32	16	4	88000	ChristinaKim
12	Software	Christina	Li	32	16	4	88000	ChristinaLi

The goal is to concatenate the fields specified in the lookup table.

Any guidance or suggestions on handling the multiple field names would be greatly appreciated!

[Luke_C · Accepted solution]

Hi @hcho 

Give this a go, hopefully it gets you started. I transposed the data and split out the field names in order to isolate just the fields that need to be concatenated. Then did the concatenation and put it back with the original dataset. 

. 

[Carolyn]

Are you saying that you want to Concatenate the FirstName & LastName columns to create a value to lookup to the lookup table? If so, I would change the FullName formula to be

[FirstName] + "," + [LastName]

instead of what looks like 

[FirstName][LastName]

in your Desired Output table. 

With the comma, then that should lookup to your Lookup table's [Field] column which is in the format of First,Last