How would *you* Align Two Tables in Excel?

[Bill Jelen]

I have a bunch of Excel videos out on YouTube that are generally well received. But recently, I've been getting hate mail from viewers of a video called "Align Two Tables".

I made the video because someone had data in A and supposedly matching data in D and wanted to align those tables. The person who asked the question wanted a way to automatically shift the data in D down so it appeared on the same row as the matching data in A. 

I don't know of a magic bullet for this.

In the video, I Use =MATCH(D2,$A$2:$A$1000,0) in E to find the ones that are missing from A, use =IFERROR(VLOOKUP(A2,$D$2:$D$1000,1,False),"") in B to mark the ones in A that are missing. It does not move the data in D, but it gives the same result as what they asked for. I've done this thousands of times in my life and to me, it is a fast way.

The critics say I wasted 5 minutes and 24 seconds of their life by not having a magic button that they could press to rearrange the data in D so it lines up. They say: "We don't want VLOOKUP, we want to align the two data sets!" 

I've marked  the video as "unlisted" so I don't waste anyone else's time until I find a better way. But this video was getting a lot of views, so it must be a common need in Excel. 

Am I missing another approach? (Other than manually looking at each record and doing Insert, Cells, Shift Cells Down a few dozen times). I even tried a macro that would iterate through D and do the Insert Cells  when A does not match D. But that macro will push all the data to row 1001 as soon as something in D does not have a match in A. 

Thanks for any suggestions or feedback. I don't want to waste 5 minutes 24 seconds of your life, but the link to the video is here: https://youtu.be/4wsWw7Alwwo

Bill

[#2]

Power Query.  No messing with formulas. No problems if new data gets added to the product table.

Create a connection to each of the two tables, then merge the two tables and extract the Sold column.

let
    Source = Table.NestedJoin(Products,{"product"},SoldProducts,{"sold product"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"sold"}, {"sold"})
in
    #"Expanded NewColumn"

No need to replace formulas with values. One click to refresh when new data is added.