I've added a new sheet ("Erik Help") with the following formula solution:
=ArrayFormula({'Data 1'!A1:C1,'Data 2'!B1:C1;{TEXT(DATE(YEAR(TODAY()),SEQUENCE(12),1),"mmm"),IFERROR(VLOOKUP(TEXT(DATE(YEAR(TODAY()),SEQUENCE(12),1),"mmm"),'Data 1'!A:C,{2,3},FALSE),{"",""}),IFERROR(VLOOKUP(TEXT(DATE(YEAR(TODAY()),SEQUENCE(12),1),"mmm"),'Data 2'!A:C,{2,3},FALSE),{"",""})}})
Explaining this fully will not be possible here. But I'll provide an overview.
Anything set between curly brackets { }
forms a virtual array (i.e., a custom grouping of data).
First, the headers are pulled from the two Data
sheets.
The semicolon means "move down to the next row and continue."
This section TEXT(DATE(YEAR(TODAY()),SEQUENCE(12),1),"mmm")
(which appears three times) forms a three-letter listing of all months, not just the ones that happen to be currently in one of the Data
sheets.
That month-list is places in the first column. In the second and third columns, that listing is looked up in Data 1
and any matching results are returned from columns 2 and 3 (i.e., {2, 3}
). The same is then repeated for the Data 2
sheet.
ADDENDUM (after new comment and changed data from OP)
The above solution was built around the original sample data keying off of clear month names.
As it turns out, the actual usage does not have months as the key data but rather names (or some other random ID).
Here is the above formula rewritten around two new sheets named Data A
and Data B
:
=ArrayFormula({'Data A'!A1:C1,'Data B'!B1:C1;SORT({UNIQUE({FILTER('Data A'!A2:A,'Data A'!A2:A<>"");FILTER('Data B'!A2:A,'Data B'!A2:A<>"")}),IFERROR(VLOOKUP(UNIQUE({FILTER('Data A'!A2:A,'Data A'!A2:A<>"");FILTER('Data B'!A2:A,'Data B'!A2:A<>"")}),'Data 1'!A:C,{2,3},FALSE),{"",""}),IFERROR(VLOOKUP(UNIQUE({FILTER('Data A'!A2:A,'Data A'!A2:A<>"");FILTER('Data B'!A2:A,'Data B'!A2:A<>"")}),'Data 2'!A:C,{2,3},FALSE),{"",""})})})