AIJOIN

[Xlambda]

AIJOIN arrays Inner Join, same functionality as in Power Querry join/merge scenario called Inner Join. Calls ATEXTJOIN , ARRANGE , APPEND2H
l, left array, r, right array
klf: link "key" of left array, integer or array of integers. 2 or {3,2} . 0 or ignored will be consider 1
krt: link "key" of right array, integer or array of integers. 2 or {3,2} . 0 or ignored will be consider 1
Other functions on minisheet ARRANGE , APIVOT

=LAMBDA(l,r,klf,krt,
    LET(d,"|",t,"not found",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),rl,ROWS(l),sl,SEQUENCE(rl),rr,ROWS(r),sr,SEQUENCE(rr),ckr,COLUMNS(kr),
       a,INDEX(l,sl,kl),b,INDEX(r,sr,kr),ta,ATEXTJOIN(a,,,d),tb,ATEXTJOIN(b,,,d),x,XMATCH(ta,tb),xn,ISNUMBER(x),ia,FILTER(sl,xn,t),ib,FILTER(x,xn),
       al,INDEX(l,ia,ql),ar,ARRANGE(INDEX(r,ib,qr),kr),
       ml,OR(ISNA(XMATCH(kl,ql))),mr,OR(ISNA(XMATCH(kr,qr))),
       IFS(OR(ml,mr),"check keys",ia=t,t,TRUE,APPEND2H(al,ar,ckr+1))
    )
)

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
1	PQ name: Inner Join (And logical test)									=AIJOIN(A2:C14,E2:H10,{3,2},{2,4})						array 1			array 2		find common names in both arrays
2	Units	Color	Product		Code	Product	Price	Color		Units	Color	Product	Code	Price		Sol Marroquin			Raven Beatty		=AIJOIN(P2:P13,S2:S13,,)
3	48	Red	Quad		CR-r	Carlota	26	Red		48	Red	Quad	QD-r	43		Kiera Mcfall			Roxanna Mercier		Raven Beatty
4	156	Blue	Quad		KK-b	Kiki	13	Blue		156	Blue	Quad	QD-b	41		Raven Beatty			Fanny Denning		Wei Lockwood
5	168	Red	Quad		QD-r	Quad	43	Red		168	Red	Quad	QD-r	43		Elinore Dees			Lesha Nobles		Angelita Packer
6	132	Blue	Carlota		SH-r	Sunshine	19	Red		108	Red	Sunshine	SH-r	19		Wei Lockwood			Wei Lockwood		Beaulah Wenger
7	72	Blue	Carlota		CR-g	Carlota	24	Green		128	Blue	Quad	QD-b	41		Donald Eldridge			Gertrudis Fitzpatrick		Malvina Hamer
8	108	Red	Sunshine		QD-b	Quad	41	Blue		96	Red	Carlota	CR-r	26		Claudio Beam			Angelita Packer
9	128	Blue	Quad		SH-b	Sunshine	18	Blue		60	Red	Sunshine	SH-r	19		Angelita Packer			Beaulah Wenger		if we invert order of arrays in formula
10	96	Red	Carlota		KK-r	Kiki	20	Red		24	Blue	Sunshine	SH-b	18		Reyna Luke			Malvina Hamer		we get the same result
11	60	Red	Sunshine							24	Blue	Quad	QD-b	41		Beaulah Wenger			Bernita Crutcher		=AIJOIN(S2:S13,P2:P13,,)
12	24	Blue	Sunshine							inner join left to right(dups in left array)						Malvina Hamer			Shiela Anaya		Raven Beatty
13	120	Blue	Carlota							Obs:	If there are duplicates of keys					Vivan Keeney			Yolonda Armstead		Wei Lockwood
14	24	Blue	Quad								on left array, all will be shown										Angelita Packer
15					error debug.					=ARRANGE(J2#,{3,4,2,5})						=APIVOT(J17:N25,1,3,5,1)			counts		Beaulah Wenger
16					=AIJOIN(A3:C14,E3:H10,4,2)					Product	Code	Color	Price	Units		(1\3) 5 vf=1	Blue	Red	Grand Total		Malvina Hamer
17					check keys					Quad	QD-r	Red	43	48		Carlota	0	1	1
18										Quad	QD-b	Blue	41	156		Quad	3	2	5
19					(key left column out of range)					Quad	QD-r	Red	43	168		Sunshine	1	2	3
20										Sunshine	SH-r	Red	19	108		Grand Total	4	5	9
21					=AIJOIN(A3:C14,E3:H10,3,4)					Quad	QD-b	Blue	41	128
22					not found					Carlota	CR-r	Red	26	96		=APIVOT(J17:N25,1,3,5,)			sum units
23										Sunshine	SH-r	Red	19	60		(1\3) 5 vf=0	Blue	Red	Grand Total
24					(keys in range but					Sunshine	SH-b	Blue	18	24		Carlota	0	96	96
25					no common data found)					Quad	QD-b	Blue	41	24		Quad	308	216	524
26																Sunshine	24	168	192
27										=AIJOIN(E2:H10,A2:C14,{2,4},{3,2})						Grand Total	332	480	812
28										Code	Product	Price	Color	Units
29										CR-r	Carlota	26	Red	96		other functions used on minisheet
30										QD-r	Quad	43	Red	48		ARRANGE
31										SH-r	Sunshine	19	Red	108		APIVOT
32										QD-b	Quad	41	Blue	156
33										SH-b	Sunshine	18	Blue	24
34										inner join right to left(dups in left array)
35										Obs:	If there are duplicates of keys
36											only first occurrence is shown
AIJOIN post

Cell Formulas
Range	Formula
J1,J27,P22,E21,E16,P15,J15,U11,U2	J1	=FORMULATEXT(J2)
J2:N11	J2	=AIJOIN(A2:C14,E2:H10,{3,2},{2,4})
U3:U7	U3	=AIJOIN(P2:P13,S2:S13,,)
U12:U16	U12	=AIJOIN(S2:S13,P2:P13,,)
J16:N25	J16	=ARRANGE(J2#,{3,4,2,5})
P16:S20	P16	=APIVOT(J17:N25,1,3,5,1)
E17	E17	=AIJOIN(A3:C14,E3:H10,4,2)
E22	E22	=AIJOIN(A3:C14,E3:H10,3,4)
P23:S27	P23	=APIVOT(J17:N25,1,3,5,)
J28:N33	J28	=AIJOIN(E2:H10,A2:C14,{2,4},{3,2})
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.

 

[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.