AJOIN

[Xlambda]

AJOIN arrays Join, same functionality as in Power Querry join/merge scenario called Full Outer. Calls ALMERGE , AMERGE , APPEND2V

=LAMBDA(l,r,klf,krt,
    LET(kl,UNIQUE(IF(klf=0,1,klf),1),kr,UNIQUE(IF(krt=0,1,krt),1),
       cl,COLUMNS(l),ql,SEQUENCE(,cl),cr,COLUMNS(r),qr,SEQUENCE(,cr),
       al,ALMERGE(r,l,kr,kl),a,AMERGE(l,r,kl,kr),c,COLUMNS(a),s,SEQUENCE(,c),y,FILTER(qr,ISNA(XMATCH(qr,kr))),
       d,COLUMNS(l),x,XLOOKUP(s,kl,kr),z,IF(s<=d,x,INDEX(y,s-d)),v,INDEX(al,SEQUENCE(ROWS(al)),z),
       ml,OR(ISNA(XMATCH(kl,ql))),mr,OR(ISNA(XMATCH(kr,qr))),
       IF(OR(ml,mr),"check keys",APPEND2V(a,v,))
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	PQ name: Full Outter (Left Outter+non matching in right array)															PQ name: Full Outter (Righ Outter+non matching in left array)
2	Units	Color	Product		Code	Product	Price	Color		=AJOIN(A2:C14,E2:H10,{3,2},{2,4})						=AJOIN(E2:H10,A2:C14,{2,4},{3,2})
3	48	Red	Quad		CR-r	Carlota	26	Red		Units	Color	Product	Code	Price		Code	Product	Price	Color	Units		array 1		array 2
4	156	Blue	Quad		KK-b	Kiki	13	Blue		48	Red	Quad	QD-r	43		CR-r	Carlota	26	Red			Sol Marroquin		Raven Beatty
5	168	Red	Quad		QD-r	Quad	43	Red		156	Blue	Quad	QD-b	41		KK-b	Kiki	13	Blue			Kiera Mcfall		Roxanna Mercier
6	132	Blue	Carlota		SH-r	Sunshine	19	Red		168	Red	Quad	QD-r	43		QD-r	Quad	43	Red	48		Raven Beatty		Fanny Denning
7	72	Blue	Carlota		CR-g	Carlota	24	Green		132	Blue	Carlota				SH-r	Sunshine	19	Red	108		Elinore Dees		Lesha Nobles
8	108	Red	Sunshine		QD-b	Quad	41	Blue		72	Blue	Carlota				CR-g	Carlota	24	Green			Wei Lockwood		Wei Lockwood
9	128	Blue	Quad		SH-b	Sunshine	18	Blue		108	Red	Sunshine	SH-r	19		QD-b	Quad	41	Blue	156		Donald Eldridge		Gertrudis Fitzpatrick
10	96	Red	Gigi		KK-r	Kiki	20	Red		128	Blue	Quad	QD-b	41		SH-b	Sunshine	18	Blue	24		Claudio Beam		Angelita Packer
11	60	Red	Sunshine							96	Red	Gigi				KK-r	Kiki	20	Red			Angelita Packer		Beaulah Wenger
12	24	Blue	Sunshine							60	Red	Sunshine	SH-r	19			Carlota		Blue	132		Reyna Luke		Malvina Hamer
13	120	Blue	Carlota							24	Blue	Sunshine	SH-b	18			Carlota		Blue	72		Beaulah Wenger		Bernita Crutcher
14	24	Blue	Quad							120	Blue	Carlota					Gigi		Red	96		Malvina Hamer		Shiela Anaya
15										24	Blue	Quad	QD-b	41			Carlota		Blue	120		Vivan Keeney		Yolonda Armstead
16											Red	Carlota	CR-r	26
17											Blue	Kiki	KK-b	13								when keys have no dups, same results both directions
18											Green	Carlota	CR-g	24								=SORT(AJOIN(V4:V15,X4:X15,,))		=SORT(AJOIN(X4:X15,V4:V15,,))
19											Red	Kiki	KK-r	20								Angelita Packer		Angelita Packer
20																						Beaulah Wenger		Beaulah Wenger
21																						Bernita Crutcher		Bernita Crutcher
22																						Claudio Beam		Claudio Beam
23																						Donald Eldridge		Donald Eldridge
24																						Elinore Dees		Elinore Dees
25																						Fanny Denning		Fanny Denning
26																						Gertrudis Fitzpatrick		Gertrudis Fitzpatrick
27																						Kiera Mcfall		Kiera Mcfall
28																						Lesha Nobles		Lesha Nobles
29																						Malvina Hamer		Malvina Hamer
30																						Raven Beatty		Raven Beatty
31																						Reyna Luke		Reyna Luke
32																						Roxanna Mercier		Roxanna Mercier
33																						Shiela Anaya		Shiela Anaya
34																						Sol Marroquin		Sol Marroquin
35																						Vivan Keeney		Vivan Keeney
36																						Wei Lockwood		Wei Lockwood
37																						Yolonda Armstead		Yolonda Armstead
38
AJOIN post

Cell Formulas
Range	Formula
J2,V18,X18,P2	J2	=FORMULATEXT(J3)
J3:N19	J3	=AJOIN(A2:C14,E2:H10,{3,2},{2,4})
P3:T15	P3	=AJOIN(E2:H10,A2:C14,{2,4},{3,2})
V19:V37	V19	=SORT(AJOIN(V4:V15,X4:X15,,))
X19:X37	X19	=SORT(AJOIN(X4:X15,V4:V15,,))
Dynamic array formulas.

 

[Xlambda]

Here are the general notes addendum of the 6 Power Querry scenarios for merging/joining tables.
4 functions will cover all 6 scenarios, for any arrays not only tables, no PQ or refresh needed: AIJOIN, ALMERGE, AMERGE, AJOIN
1. Inner Join (AND logical test) , our function AIJOIN
2. Left Anti (Only in left array), our function ALMERGE
3. Left Outer (Classic Lookup in right array), our function AMERGE (most common)
4. Right Anti (Only in right array), we can use ALMERGE inverting the order of arrays in the formula
5. Right Outer (Classic Lookup in left array), we can use AMERGE inverting the order of arrays in the formula
6. Full Outer (OR logical text), our function AJOIN
The "links" for this types of joining/merging are called "keys", primary (unique) or foreign keys. They represents common column or multiple columns found in both arrays (they share the same type of data of the respective columns and should have same dimension, in most of the cases).
The functions can handle also dups of keys along with unique keys, and for that reason we can have different outcomes if we apply same functions from left to right or right to left. Plenty of examples on minisheets to reveal this behavior.
Attached picture, courtesy of Mike Girvin ExcelIsFun together with some of the sample data found on these posts.
View attachment 37624

 

[Xlambda]

2nd addendum: All the formulas except AJOIN, can handle multiple columns "keys". In most cases they should have the same dimension. Anyhow, the functions can handle dif dimension "keys", but only if we are positive that their join values are similar and can be matched. As an example, an array can have as key, 2 sep columns, first names and last names, and the other array has only a single column key for first names and last names joined with a delimiter in between. The default delimiter used in my formula is "|", did not used it as a separate argument, but can be changed inside formulas. It is variable "d" defined after LET. Or we can use our text manipulation formulas to change any delimiter with "|". It has powerful versatility but has to be handled with care.

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	different keys dimensions scenario					array 1					array 2				Left Anti (Only in left array)					Right Anti (Only in right array)
2	list 1 sample		list 2 sample,dif delimiters			First Name	Last Name	Units			Name	Price			=ALMERGE(F3:H14,K3:L14,{1,2},)					=ALMERGE(K3:L14,F3:H14,,{1,2})
3	Sol Marroquin		Raven,Beatty			Sol	Marroquin	29			Raven|Beatty	123			First Name	Last Name	Units			Name	Price
4	Kiera Mcfall		Roxanna Mercier			Kiera	Mcfall	14			Roxanna|Mercier	213			Sol	Marroquin	29			Roxanna|Mercier	213
5	Raven Beatty		Fanny,Denning			Raven	Beatty	23			Fanny|Denning	323			Kiera	Mcfall	14			Fanny|Denning	323
6	Elinore Dees		Lesha Nobles			Elinore	Dees	15			Lesha|Nobles	211			Elinore	Dees	15			Lesha|Nobles	211
7	Wei Lockwood		Wei/Lockwood			Wei	Lockwood	19			Wei|Lockwood	124			Donald	Eldridge	24			Gertrudis|Fitzpatrick	245
8	Donald Eldridge		Gertrudis Fitzpatrick			Donald	Eldridge	24			Gertrudis|Fitzpatrick	245			Claudio	Beam	34			Bernita|Crutcher	100
9	Claudio Beam		Angelita Packer			Claudio	Beam	34			Angelita|Packer	267			Reyna	Luke	7			Shiela|Anaya	110
10	Angelita Packer		Beaulah Wenger			Angelita	Packer	23			Beaulah|Wenger	213			Vivan	Keeney	14			Yolonda|Armstead	423
11	Reyna Luke		Malvina Hamer			Reyna	Luke	7			Malvina|Hamer	321
12	Beaulah Wenger		Bernita\Crutcher			Beaulah	Wenger	8			Bernita|Crutcher	100			Inner Join (And logical test)					Inner Join (And logical test)
13	Malvina Hamer		Shiela Anaya			Malvina	Hamer	11			Shiela|Anaya	110			=AIJOIN(F3:H14,K3:L14,{1,2},)					=AIJOIN(K3:L14,F3:H14,,{1,2})
14	Vivan Keeney		Yolonda Armstead			Vivan	Keeney	14			Yolonda|Armstead	423			First Name	Last Name	Units	Price		Name	Price	Units
15															Raven	Beatty	23	123		Raven|Beatty	123	23
16	functions					Left Outter (Classic lookup in right array)					Right Outter (Classic lookup in left array)				Wei	Lockwood	19	124		Wei|Lockwood	124	19
17	used in minisheet		formula to change all delimiters			=AMERGE(F3:H14,K3:L14,{1,2},)					=AMERGE(K3:L14,F3:H14,1,{1,2})				Angelita	Packer	23	267		Angelita|Packer	267	23
18	AREMOVE		=AREMOVE(C3:C14,T_CHARS(,,", /\"),"|")			Name	Last Name	Units	Price		Name	Price	Units		Beaulah	Wenger	8	213		Beaulah|Wenger	213	8
19	T_CHARS		Raven|Beatty			Sol	Marroquin	29			Raven|Beatty	123	23		Malvina	Hamer	11	321		Malvina|Hamer	321	11
20	AMERGE		Roxanna|Mercier			Kiera	Mcfall	14			Roxanna|Mercier	213			Inner Join has the same outcome in different structures
21	ALMERGE		Fanny|Denning			Raven	Beatty	23	123		Fanny|Denning	323
22	AIJOIN		Lesha|Nobles			Elinore	Dees	15			Lesha|Nobles	211			Due to the way the functions are constructed, AJOIN can not be used
23	AJOIN		Wei|Lockwood			Wei	Lockwood	19	124		Wei|Lockwood	124	19		with different keys dimensions
24			Gertrudis|Fitzpatrick			Donald	Eldridge	24			Gertrudis|Fitzpatrick	245			=AJOIN(F3:H14,K3:L14,{1,2},1)					=AJOIN(K3:L14,F3:H14,,{1,2})
25			Angelita|Packer			Claudio	Beam	34			Angelita|Packer	267	23		First Name	Last Name	Units	Price		Name	Price	Units
26			Beaulah|Wenger			Angelita	Packer	23	267		Beaulah|Wenger	213	8		Sol	Marroquin	29			Raven|Beatty	123	23
27			Malvina|Hamer			Reyna	Luke	7			Malvina|Hamer	321	11		Kiera	Mcfall	14			Roxanna|Mercier	213
28			Bernita|Crutcher			Beaulah	Wenger	8	213		Bernita|Crutcher	100			Raven	Beatty	23	123		Fanny|Denning	323
29			Shiela|Anaya			Malvina	Hamer	11	321		Shiela|Anaya	110			Elinore	Dees	15			Lesha|Nobles	211
30			Yolonda|Armstead			Vivan	Keeney	14			Yolonda|Armstead	423			Wei	Lockwood	19	124		Wei|Lockwood	124	19
31															Donald	Eldridge	24			Gertrudis|Fitzpatrick	245
32															Claudio	Beam	34			Angelita|Packer	267	23
33															Angelita	Packer	23	267		Beaulah|Wenger	213	8
34															Reyna	Luke	7			Malvina|Hamer	321	11
35															Beaulah	Wenger	8	213		Bernita|Crutcher	100
36															Malvina	Hamer	11	321		Shiela|Anaya	110
37															Vivan	Keeney	14			Yolonda|Armstead	423
38															#VALUE!	#VALUE!	#VALUE!	213		Sol		29
39															#VALUE!	#VALUE!	#VALUE!	323		Kiera		14
40															#VALUE!	#VALUE!	#VALUE!	211		Elinore		15
41															#VALUE!	#VALUE!	#VALUE!	245		Donald		24
42															#VALUE!	#VALUE!	#VALUE!	100		Claudio		34
43															#VALUE!	#VALUE!	#VALUE!	110		Reyna		7
44															#VALUE!	#VALUE!	#VALUE!	423		Vivan		14
45
Key dif dim post

Cell Formulas
Range	Formula
O2,T24,O24,K17,F17,T13,O13,T2	O2	=FORMULATEXT(O4)
F3:G14	F3	=ASPLIT(A3:A14," ")
K3:K14	K3	=AREMOVE(C3:C14,T_CHARS(,,", /\"),"|")
O4:Q10	O4	=ALMERGE(F3:H14,K3:L14,{1,2},)
T4:U10	T4	=ALMERGE(K3:L14,F3:H14,,{1,2})
O15:R19	O15	=AIJOIN(F3:H14,K3:L14,{1,2},)
T15:V19	T15	=AIJOIN(K3:L14,F3:H14,,{1,2})
C18	C18	=FORMULATEXT(C19)
C19:C30	C19	=AREMOVE(C3:C14,T_CHARS(,,", /\"),"|")
F19:I30	F19	=AMERGE(F3:H14,K3:L14,{1,2},)
K19:M30	K19	=AMERGE(K3:L14,F3:H14,1,{1,2})
O26:R44	O26	=AJOIN(F3:H14,K3:L14,{1,2},1)
T26:V44	T26	=AJOIN(K3:L14,F3:H14,,{1,2})
Dynamic array formulas.