I am trying to do the full join for the data below in two different sheets.

Sheet 9:

Sheet10

Desired output sheet :

Please also share if we have more columns to join like in sheet 1 and sheet 2 has two more columns like Year, product label etc then what should I change in your proposed solution

I am using this formula but its not returning the desired result

=ARRAYFORMULA({QUERY(SORT(UNIQUE({Sheet9!A1:D; Sheet10!A1:D})), "where Col1 is not null"),IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(SORT(UNIQUE({Sheet9!A1:D; Sheet10!A1:D})), "where Col1 is not null")),,999^99)), TRANSPOSE(QUERY(TRANSPOSE(Sheet9!A1:D),,999^99)), Sheet9!C1:C}, 2, 0),""),IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(SORT(UNIQUE({Sheet9!A1:D; Sheet10!A1:D})), "where Col1 is not null")),,999^99)), {TRANSPOSE(QUERY(TRANSPOSE(Sheet10!A1:D),,999^99)), Sheet10!C1:C}, 2, 0),"")}})

CodePudding user response：

EDITED to consider dynamic row matching.

See this spreadsheet to illustration, but overall there's a question of your setup, but I would break your problem into two steps.

Get distinct list of ID's

You can get that with this formula:

=unique(transpose(split(textjoin(",",true,
    iferror(INdex(Sheet2!$A$2:$Z,0,MATCH(A1,Sheet2!1:1,0)),""),
    iferror(INdex(Sheet1!$A$2:$Z,0,MATCH(A1,Sheet1!1:1,0)),"")),",")))

Rest of Headers

Then for each header, will they each always only be in 1 exclusively or 2 (not both)? Assuming so, this should work for each additional column. If two values ever exist in the two sheets, will join them in the same column.

=filter( 
 iferror(VLOOKUP($A$2:$A,Sheet1!$A:$Z,match(E$1,Sheet1!1:1,0),false),"")
 &iferror(VLOOKUP($A$2:$A,Sheet2!$A:$Z,match(E$1,Sheet2!1:1,0),false),"")
   ,$A$2:$A<>"")

There's probably a way to use the join function to do this more elegantly (if someone posts an answer showing me I'll upvote).