How to try a Power Query merge or append instead of vlookups?

[surfdoc37]

I have two sheets to combine. The first with about 1000 rows and 25 columns of values. The second with about 300 rows and a dozen columns. The columns on each sheet are different. Entries in column A of the second sheet are a subset of the first sheet.

I can make a third sheet with a bunch of vlookups across the top to include 300 names, two columns from the second sheet, and 20 columns from the first sheet.

I pull the data into each sheet from csv files downloaded to my desktop.

Trying to figure out if I could more elegantly use maybe the "merge" or "append" function (or something else) under the "get and transform data" to effectively just pull the data I want--the rows from sheet 2, and selected columns from both sheet 1 and sheet 2-- directly into the combined third sheet.

Appreciate any ideas.

 

[KRice]

I would definitely look into Power Query for this. I believe you will want to Merge Query as New to create a new table consisting of columns from both tables using the common column for matching. You may want the Join type to be FullOuter to return all rows from both tables (so any unmatched records are still shown, but nulls will be added to fill in empty cells). Post back if you need some help setting this up.