[SOLVED] Google sheets: How to merge two Google sheets with duplicate keys?

Google sheets: How to merge two Google sheets with duplicate keys?

I have two sheets, each containing a table with duplicate keys. I want to create a third sheet that merges these two tables such that each row represents a cross-product of the data for each key.

Here's an example of my data:

Sheet1:

Key	Data
A	1
B	2
C	3
A	4
D	5

Sheet2:

Key	Data
B	6
C	7
D	8
A	9
A	10

I want to create a third sheet that looks like this:

Merged:

Key	Data (Sheet1)	Data (Sheet2)
A	1	9
A	1	10
A	4	9
A	4	10
B	2	6
C	3	7
D	5	8

How can I achieve this? Thanks in advance

I have been trying an INDEX and MATCH combination, but given that keys are duplicated I only obtain the first value.

Solution

I put everything in one sheet, so it's more easily viewable, please adapt the range so it represents the two sheets:

=LET(keys,TOCOL(UNIQUE(A2:A),1),
REDUCE(HSTACK("Key","Data1","Data2"),keys,LAMBDA(a,key,
  VSTACK(a,REDUCE(TOCOL(,1),FILTER(B2:B,A2:A=key),LAMBDA(b,each1,
  VSTACK(b,BYROW(FILTER(E2:E,D2:D=key),LAMBDA(each2,  HSTACK(key,each1,each2))))))))))