PAIRWISE

[tboulden]

PAIRWISE leverages MAKEARRAY to lift complex formulas across an array of rows and an array of columns.

=LAMBDA(array_1,array_2,fn,
  MAKEARRAY(
    ROWS(array_1),
    COLUMNS(array_2),
    LAMBDA(i,j,
      LET(
        cols_array_1,COLUMNS(array_1),
        rows_array_2,ROWS(array_2),
        fn(
          INDEX(array_1,i,IF(cols_array_1=1,1,SEQUENCE(,cols_array_1))),
          INDEX(array_2,IF(rows_array_2=1,1,SEQUENCE(rows_array_2)),j)
        )
      )
    )
  )
)

LAMBDA_pairwise_thunks_summarize_pivot.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	Z	AA	AB	AC	AD
1		"+" lifts over 1d arrays						=B3#+C2#							"=" lifts over arrays						=O3#=P2#
2			1	2	3	4		=pairWise(B3#,C2#,LAMBDA(x,y,x+y))								4	3	2	1		=pairWise(O3#,P2#,LAMBDA(x,y,x=y))
3		1	2	3	4	5		2	3	4	5	TRUE			1	FALSE	FALSE	FALSE	TRUE		FALSE	FALSE	FALSE	TRUE		TRUE
4		2	3	4	5	6		3	4	5	6				2	FALSE	FALSE	TRUE	FALSE		FALSE	FALSE	TRUE	FALSE
5		3	4	5	6	7		4	5	6	7				3	FALSE	TRUE	FALSE	FALSE		FALSE	TRUE	FALSE	FALSE
6		4	5	6	7	8		5	6	7	8				4	TRUE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE
7
8		"&" lifts over arrays						=B10#&C9#							Complex formulae won’t lift						=IF(ISEVEN(O10#),REPT(P9#,O10#),"")
9			E	F	G	H		=pairWise(B10#,C9#,LAMBDA(x,y,x&y))								A	B	C	D		=pairWise(O10#,P9#,LAMBDA(x,y,IF(ISEVEN(x),REPT(y,x),"")))
10		A	AE	AF	AG	AH		AE	AF	AG	AH	TRUE			1	#VALUE!
11		B	BE	BF	BG	BH		BE	BF	BG	BH				2						AA	BB	CC	DD
12		C	CE	CF	CG	CH		CE	CF	CG	CH				3
13		D	DE	DF	DG	DH		DE	DF	DG	DH				4						AAAA	BBBB	CCCC	DDDD
14
15		"+" doesn't lift over >1d						=B18#+D16#
16				3	12	14		=pairWise(B18#,D16#, LAMBDA(row_,col_,SUM(row_,col_)))							1	A	red		1	A	red		=REDUCE( , pairWise(O16#,TRANSPOSE(Q16#), LAMBDA(x,y, thunkIt(hStack(x,y)))), LAMBDA(stacked,thunk, vStack( IFERROR(stacked(),stacked), thunk())))
17				12	7	10									2	B	blue		1	A	blue
18		8	19	11	31	#N/A		42	46	51		TRUE			3	C	green		1	A	green
19		2	17	14	24	#N/A		34	38	43									2	B	red
20		14	6	#N/A	#N/A	#N/A		35	39	44									2	B	blue
21		7	6	#N/A	#N/A	#N/A		28	32	37									2	B	green
22																			3	C	red
23								42	46	51									3	C	blue
24								34	38	43									3	C	green
25								35	39	44
26								28	32	37
pairWise

Cell Formulas
Range	Formula
C2:F2	C2	=SEQUENCE(,4)
H1,U8,H8,U1	H1	=FORMULATEXT(C3)
H2,U9,H9,U2	H2	=FORMULATEXT(H3)
P2:S2	P2	=SEQUENCE(,4,4,-1)
B3:B6,O10:O13,O3:O6	B3	=SEQUENCE(4)
C3:F6	C3	=B3#+C2#
H3:K6	H3	=pairWise(B3#,C2#,LAMBDA(x,y,x+y))
L3,L10	L3	=AND(C3#=H3#)
P3:S6	P3	=O3#=P2#
U3:X6	U3	=pairWise(O3#,P2#,LAMBDA(x,y,x=y))
Z3	Z3	=AND(P3#=U3#)
C9:F9	C9	=CHAR(SEQUENCE(,4,69,1))
P9:S9	P9	=CHAR(SEQUENCE(,4,65,1))
B10:B13	B10	=CHAR(SEQUENCE(4,1,65,1))
C10:F13	C10	=B10#&C9#
H10:K13	H10	=pairWise(B10#,C9#,LAMBDA(x,y,x&y))
P10	P10	=IF(ISEVEN(O10#),REPT(P9#,O10#),"")
U10:X13	U10	=pairWise(O10#,P9#,LAMBDA(x,y,IF(ISEVEN(x),REPT(y,x),"")))
D16:F17	D16	={3,12,14;12,7,10}
H15	H15	=FORMULATEXT(D18)
H16	H16	=FORMULATEXT(H18)
O16:P18	O16	={1,"A";2,"B";3,"C"}
Q16:Q18	Q16	={"red";"blue";"green"}
S16:U24	S16	=REDUCE( , pairWise(O16#,TRANSPOSE(Q16#), LAMBDA(x,y, thunkIt(hStack(x,y)))), LAMBDA(stacked,thunk, vStack( IFERROR(stacked(),stacked), thunk())))
W16	W16	=FORMULATEXT(S16)
B18:C21	B18	={8,19;2,17;14,6;7,6}
D18:F21	D18	=B18#+D16#
H18:J21	H18	=pairWise(B18#,D16#, LAMBDA(row_,col_,SUM(row_,col_)))
L18	L18	=AND(MAP(H18#,H23:J26,LAMBDA(x,y,x=y)))
H23:J26	H23	=SUM($B18:$C18,D$16:D$17)
Dynamic array formulas.