How to return/bring values from multiple data table based on lookup criteria as single column

Hey Eyelyn Qin, Thanks for you efforts, the solution works until unless data type of 'Reference' column from File 1 changed to 'Text'.

I realized that 'SUMMARIZE' funtion creating 'Code' data type as Σ which I am unable to convert to 'Text' 

Is there possible solution to if data type is 'Text' for 'Referecnce and Code' column?

Thank you again!

Hi kinmin ,

Actually as long as the types of the columns to be matched in several tables are the same, it does not matter whether it is the Text type or the Number type.

Code in New Table will be the same type as well.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.