AIF

[Xlambda]

AIF Array Include argument for Filter function, NEW!! REDUCE. Returns array of 0 and 1 useful in FILTER function as include argument. Calls AFLAT , CP
Also, as "IF" in the function name suggests, (Array IF), same function will be used in replacing COUNTIFS,SUMIFS,…all …IFS functions complex constructions. For now, will cover what the function can do as Include argument in Filter function.
The goal of the function is to be able to do compact complex boolean logic with arrays of criteria that we can write explicitly or even can live in spreadsheet ranges for dashboard constructions.
a: array
e: expression criteria argument array ( "<=3" ,3, or {">=3",4,"*ab"} )
[o]: operation argument, 0 or omitted, multiplication or boolean AND ; 1, adding or boolean OR
For every criteria found in expression criteria argument array "e", applies the comparison function CP and multiplies or adds the results , depending on "o" argument value.
If "e" array has 3 elements e1,e2,e3, and "o" is 0 or omitted :
AIF(a,e)=CP(a,e1)*CP(a,e2)*CP(a,e3)
If "o"=1 :
AIF(a,e,1)=CP(a,e1)+CP(a,e2)+CP(a,e3)

=LAMBDA(a,e,[o],LET(f,AFLAT(e),r,ROWS(f),IF(r=1,CP(a,e),LET(x,REDUCE(0,SEQUENCE(r),LAMBDA(v,i,v+CP(a,INDEX(f,i)))),IF(o,IF(x,1,0),IF(x=r,1,0))))))

CP(a,e) ComParison function. a,e arguments as in AIF

=LAMBDA(a,e,LET(x,LEFT(e,2),y,LEFT(e,1),z,SWITCH(x,"<=",x,">=",x,"<>",x,SWITCH(y,"<",y,">",y,"*",y,"!",y,"=")),w,SUBSTITUTE(e,z,"",1), v,IFERROR(--w,w),
    --SWITCH(z,"<>",a<>v,"<=",a<=v,">=",a>=v,"<",a<v,">",a>v,"*",ISNUMBER(SEARCH(v,a)),"!",ISNUMBER(FIND(v,a)),"=",a=v)
    )
)

 

[Xlambda]

Will start with CP function examples, then will continue with AIF examples and a brand new ADVFLT Advanced Filter function will follow. Lot of real life examples with tables, arrays, complex criteria.

AIF.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
1	Introducing CP(a,e), comparison function
2	a: array
3	e: expression criteria (can include comparison operators, wildcard chars)
4
5	What the function does is very basic --(array(operator)values) returning array of 0's and 1's
6	The function can split the expression argument into operators and values. If no operator is detected "=" operation occurs
7	Note: If one of the arguments, a, or e, is a 2D array, the other one should be a single element, for the result to spill.
8	If both "a" and "e" are 1D arrays, the result can spill if they are perpendicular.
9
10	List of operators: < , > , <= , >= , <> , * , !
11
12	CP Part 1. "a" 2D array,"e" single element
13									e,">=0"												e,"<=-2"
14			a		=--(A15:C19>=0)				=CP(A15:C19,">=0")						a		=--(M15:O19<=-2)				=CP(M15:O19,"<=-2")
15	2	1	4		1	1	1		1	1	1		2	1	4		0	0	0		0	0	0
16	-4	-4	4		0	0	1		0	0	1		-4	-4	4		1	1	0		1	1	0
17	-1	5	0		0	1	1		0	1	1		-1	5	0		0	0	0		0	0	0
18	0	-1	-1		1	0	0		1	0	0		0	-1	-1		0	0	0		0	0	0
19	-4	2	-2		0	1	0		0	1	0		-4	2	-2		1	0	1		1	0	1
20
21									e,"<>-4"												e,-4
22			a		=--(A23:C27<>-4)				=CP(A23:C27,"<>-4")						a		=--(M23:O27=-4)				=CP(M23:O27,-4)
23	2	1	4		1	1	1		1	1	1		2	1	4		0	0	0		0	0	0
24	-4	-4	4		0	0	1		0	0	1		-4	-4	4		1	1	0		1	1	0
25	-1	5	0		1	1	1		1	1	1		-1	5	0		0	0	0		0	0	0
26	0	-1	-1		1	1	1		1	1	1		0	-1	-1		0	0	0		0	0	0
27	-4	2	-2		0	1	1		0	1	1		-4	2	-2		1	0	0		1	0	0
28
29	CP Part 2. "a" single element, "e" 2D array
30
31	a				e				=CP(A32,C32:E33)
32	3		>=3	<=3	3				1	1	1
33			<>4	<3	>3				1	0	0
34
CP post 1

Cell Formulas
Range	Formula
E14,I14,I31,Q22,U22,E22,I22,Q14,U14	E14	=FORMULATEXT(E15)
E15:G19	E15	=--(A15:C19>=0)
I15:K19	I15	=CP(A15:C19,">=0")
Q15:S19	Q15	=--(M15:O19<=-2)
U15:W19	U15	=CP(M15:O19,"<=-2")
E23:G27	E23	=--(A23:C27<>-4)
I23:K27	I23	=CP(A23:C27,"<>-4")
Q23:S27	Q23	=--(M23:O27=-4)
U23:W27	U23	=CP(M23:O27,-4)
I32:K33	I32	=CP(A32,C32:E33)
Dynamic array formulas.

 

[Xlambda]

AIF.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
1	CP Part 3. "a" and "e" 1D arrays, perpendicular									a	ꓕ	e
2							e																			a
3	a	-3	<-3	>-3	<=-3	>=-3	<>-3		e	-7	-6	-5	-4	-3	-3	-2	-1	0	1	2	3	3	4	5	6	7
4	-7	0	1	0	1	0	1		-3	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0
5	-6	0	1	0	1	0	1		<-3	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0
6	-5	0	1	0	1	0	1		>-3	0	0	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1
7	-4	0	1	0	1	0	1		<=-3	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0
8	-3	1	0	0	1	1	0		>=-3	0	0	0	0	1	1	1	1	1	1	1	1	1	1	1	1	1
9	-3	1	0	0	1	1	0		<>-3	1	1	1	1	0	0	1	1	1	1	1	1	1	1	1	1	1
10	-2	0	0	1	0	1	1
11	-1	0	0	1	0	1	1			single cell in J4:			=CP(J3:Z3,I4:I9)
12	0	0	0	1	0	1	1
13	1	0	0	1	0	1	1		Imp. Note: Even if CP can spill, if "e" has more elements, does not do boolean logic btwn "e" elements,
14	2	0	0	1	0	1	1		like AIF does, returns a single result for each element of "e"
15	3	0	0	1	0	1	1
16	3	0	0	1	0	1	1			-7	-6	-5	-4	-3	-3	-2	-1	0	1	2	3	3	4	5	6	7
17	4	0	0	1	0	1	1		-3	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0
18	5	0	0	1	0	1	1
19	6	0	0	1	0	1	1		I designed CP to spill on 2 directions , (if they are perpendicular) because can be useful.
20	7	0	0	1	0	1	1
21
22		single cell in B4:			=CP(A4:A20,B3:G3)
23
CP post 2

Cell Formulas
Range	Formula
B4:G20	B4	=CP(A4:A20,B3:G3)
J4:Z9	J4	=CP(J3:Z3,I4:I9)
M11	M11	=FORMULATEXT(J4)
J17:Z17	J17	=CP(J16:Z16,I17)
E22	E22	=FORMULATEXT(B4)
Dynamic array formulas.

 

[Xlambda]

AIF.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	AE	AF	AG	AH
1	CP Part 4. Handling dates			Data obtained with		=RANDARRAY(10,10,"1-1-18","31-12-21",1)						a,a , e,>=1-1-21											CP can handle dates if they are in any proper date format.
2	a											=CP(A3:J12,">=1-1-21")											=CP(A3:J12,">=1/Jan/21")					a,a, e,>=1/Jan/21
3	20-04-19	02-01-18	08-12-19	13-04-20	26-12-21	03-01-21	12-02-18	26-09-21	19-12-19	26-09-18		0	0	0	0	1	1	0	1	0	0		0	0	0	0	1	1	0	1	0	0
4	16-11-19	09-01-19	02-11-20	03-11-19	08-05-18	08-02-19	10-04-19	22-09-20	23-03-21	02-02-18		0	0	0	0	0	0	0	0	1	0		0	0	0	0	0	0	0	0	1	0
5	12-02-21	01-03-20	05-11-21	21-10-18	19-04-19	18-09-19	24-08-21	29-01-21	13-02-21	30-10-20		1	0	1	0	0	0	1	1	1	0		1	0	1	0	0	0	1	1	1	0
6	24-11-21	20-12-20	22-09-19	24-12-18	28-11-19	20-11-20	12-06-21	02-02-18	17-09-18	12-04-20		1	0	0	0	0	0	1	0	0	0		1	0	0	0	0	0	1	0	0	0
7	24-08-19	08-06-21	22-03-19	14-01-20	05-11-19	15-03-21	28-01-20	30-10-20	21-12-18	05-03-21		0	1	0	0	0	1	0	0	0	1		0	1	0	0	0	1	0	0	0	1
8	03-03-21	23-11-20	06-11-21	09-04-18	30-08-20	15-03-20	25-02-21	07-04-20	13-02-21	25-11-18		1	0	1	0	0	0	1	0	1	0		1	0	1	0	0	0	1	0	1	0
9	17-09-18	30-01-19	23-01-19	27-07-20	03-09-20	21-03-19	03-01-19	20-11-21	11-02-20	06-02-21		0	0	0	0	0	0	0	1	0	1		0	0	0	0	0	0	0	1	0	1
10	06-08-18	02-02-21	28-08-20	30-09-21	07-06-21	17-01-18	08-08-18	01-03-19	09-01-18	24-05-18		0	1	0	1	1	0	0	0	0	0		0	1	0	1	1	0	0	0	0	0
11	30-04-20	18-11-19	11-05-21	02-03-18	01-06-21	24-02-20	19-09-18	14-10-20	15-12-20	07-05-19		0	0	1	0	1	0	0	0	0	0		0	0	1	0	1	0	0	0	0	0
12	15-10-20	07-02-19	07-10-19	08-03-18	13-07-21	29-04-20	12-09-18	05-08-18	08-04-18	20-03-20		0	0	0	0	1	0	0	0	0	0		0	0	0	0	1	0	0	0	0	0
13											"a" argument can handle array calculations, something that range arguments in COUNTIFS or SUMIFS can not.
14												a,YEAR(a), e,>=2021											check
15	a											=CP(YEAR(A16:J25),">=2021")											=DAY(A29:J38)
16	20-04-19	02-01-18	08-12-19	13-04-20	26-12-21	03-01-21	12-02-18	26-09-21	19-12-19	26-09-18		0	0	0	0	1	1	0	1	0	0		20	2	8	13	26	3	12	26	19	26
17	16-11-19	09-01-19	02-11-20	03-11-19	08-05-18	08-02-19	10-04-19	22-09-20	23-03-21	02-02-18		0	0	0	0	0	0	0	0	1	0		16	9	2	3	8	8	10	22	23	2
18	12-02-21	01-03-20	05-11-21	21-10-18	19-04-19	18-09-19	24-08-21	29-01-21	13-02-21	30-10-20		1	0	1	0	0	0	1	1	1	0		12	1	5	21	19	18	24	29	13	30
19	24-11-21	20-12-20	22-09-19	24-12-18	28-11-19	20-11-20	12-06-21	02-02-18	17-09-18	12-04-20		1	0	0	0	0	0	1	0	0	0		24	20	22	24	28	20	12	2	17	12
20	24-08-19	08-06-21	22-03-19	14-01-20	05-11-19	15-03-21	28-01-20	30-10-20	21-12-18	05-03-21		0	1	0	0	0	1	0	0	0	1		24	8	22	14	5	15	28	30	21	5
21	03-03-21	23-11-20	06-11-21	09-04-18	30-08-20	15-03-20	25-02-21	07-04-20	13-02-21	25-11-18		1	0	1	0	0	0	1	0	1	0		3	23	6	9	30	15	25	7	13	25
22	17-09-18	30-01-19	23-01-19	27-07-20	03-09-20	21-03-19	03-01-19	20-11-21	11-02-20	06-02-21		0	0	0	0	0	0	0	1	0	1		17	30	23	27	3	21	3	20	11	6
23	06-08-18	02-02-21	28-08-20	30-09-21	07-06-21	17-01-18	08-08-18	01-03-19	09-01-18	24-05-18		0	1	0	1	1	0	0	0	0	0		6	2	28	30	7	17	8	1	9	24
24	30-04-20	18-11-19	11-05-21	02-03-18	01-06-21	24-02-20	19-09-18	14-10-20	15-12-20	07-05-19		0	0	1	0	1	0	0	0	0	0		30	18	11	2	1	24	19	14	15	7
25	15-10-20	07-02-19	07-10-19	08-03-18	13-07-21	29-04-20	12-09-18	05-08-18	08-04-18	20-03-20		0	0	0	0	1	0	0	0	0	0		15	7	7	8	13	29	12	5	8	20
26
27												a,MONTH(a), e,"<=6"											a,DAY(a), e,">20"
28	a											=CP(MONTH(A29:J38),"<=6")											=CP(DAY(A29:J38),">20")
29	20-04-19	02-01-18	08-12-19	13-04-20	26-12-21	03-01-21	12-02-18	26-09-21	19-12-19	26-09-18		1	1	0	1	0	1	1	0	0	0		0	0	0	0	1	0	0	1	0	1
30	16-11-19	09-01-19	02-11-20	03-11-19	08-05-18	08-02-19	10-04-19	22-09-20	23-03-21	02-02-18		0	1	0	0	1	1	1	0	1	1		0	0	0	0	0	0	0	1	1	0
31	12-02-21	01-03-20	05-11-21	21-10-18	19-04-19	18-09-19	24-08-21	29-01-21	13-02-21	30-10-20		1	1	0	0	1	0	0	1	1	0		0	0	0	1	0	0	1	1	0	1
32	24-11-21	20-12-20	22-09-19	24-12-18	28-11-19	20-11-20	12-06-21	02-02-18	17-09-18	12-04-20		0	0	0	0	0	0	1	1	0	1		1	0	1	1	1	0	0	0	0	0
33	24-08-19	08-06-21	22-03-19	14-01-20	05-11-19	15-03-21	28-01-20	30-10-20	21-12-18	05-03-21		0	1	1	1	0	1	1	0	0	1		1	0	1	0	0	0	1	1	1	0
34	03-03-21	23-11-20	06-11-21	09-04-18	30-08-20	15-03-20	25-02-21	07-04-20	13-02-21	25-11-18		1	0	0	1	0	1	1	1	1	0		0	1	0	0	1	0	1	0	0	1
35	17-09-18	30-01-19	23-01-19	27-07-20	03-09-20	21-03-19	03-01-19	20-11-21	11-02-20	06-02-21		0	1	1	0	0	1	1	0	1	1		0	1	1	1	0	1	0	0	0	0
36	06-08-18	02-02-21	28-08-20	30-09-21	07-06-21	17-01-18	08-08-18	01-03-19	09-01-18	24-05-18		0	1	0	0	1	1	0	1	1	1		0	0	1	1	0	0	0	0	0	1
37	30-04-20	18-11-19	11-05-21	02-03-18	01-06-21	24-02-20	19-09-18	14-10-20	15-12-20	07-05-19		1	0	1	1	1	1	0	0	0	1		1	0	0	0	0	1	0	0	0	0
38	15-10-20	07-02-19	07-10-19	08-03-18	13-07-21	29-04-20	12-09-18	05-08-18	08-04-18	20-03-20		0	1	0	1	0	1	0	0	1	1		0	0	0	0	0	1	0	0	0	0
39												Which days are weekends?
40												a,WEEKDAY(a,2), e,">=5"											check
41	a											=CP(WEEKDAY(A42:J51,2),">=5")											=TEXT(A42:J51,"ddd")
42	20-04-19	02-01-18	08-12-19	13-04-20	26-12-21	03-01-21	12-02-18	26-09-21	19-12-19	26-09-18		1	0	1	0	1	1	0	1	0	0		Sat	Tue	Sun	Mon	Sun	Sun	Mon	Sun	Thu	Wed
43	16-11-19	09-01-19	02-11-20	03-11-19	08-05-18	08-02-19	10-04-19	22-09-20	23-03-21	02-02-18		1	0	0	1	0	1	0	0	0	1		Sat	Wed	Mon	Sun	Tue	Fri	Wed	Tue	Tue	Fri
44	12-02-21	01-03-20	05-11-21	21-10-18	19-04-19	18-09-19	24-08-21	29-01-21	13-02-21	30-10-20		1	1	1	1	1	0	0	1	1	1		Fri	Sun	Fri	Sun	Fri	Wed	Tue	Fri	Sat	Fri
45	24-11-21	20-12-20	22-09-19	24-12-18	28-11-19	20-11-20	12-06-21	02-02-18	17-09-18	12-04-20		0	1	1	0	0	1	1	1	0	1		Wed	Sun	Sun	Mon	Thu	Fri	Sat	Fri	Mon	Sun
46	24-08-19	08-06-21	22-03-19	14-01-20	05-11-19	15-03-21	28-01-20	30-10-20	21-12-18	05-03-21		1	0	1	0	0	0	0	1	1	1		Sat	Tue	Fri	Tue	Tue	Mon	Tue	Fri	Fri	Fri
47	03-03-21	23-11-20	06-11-21	09-04-18	30-08-20	15-03-20	25-02-21	07-04-20	13-02-21	25-11-18		0	0	1	0	1	1	0	0	1	1		Wed	Mon	Sat	Mon	Sun	Sun	Thu	Tue	Sat	Sun
48	17-09-18	30-01-19	23-01-19	27-07-20	03-09-20	21-03-19	03-01-19	20-11-21	11-02-20	06-02-21		0	0	0	0	0	0	0	1	0	1		Mon	Wed	Wed	Mon	Thu	Thu	Thu	Sat	Tue	Sat
49	06-08-18	02-02-21	28-08-20	30-09-21	07-06-21	17-01-18	08-08-18	01-03-19	09-01-18	24-05-18		0	0	1	0	0	0	0	1	0	0		Mon	Tue	Fri	Thu	Mon	Wed	Wed	Fri	Tue	Thu
50	30-04-20	18-11-19	11-05-21	02-03-18	01-06-21	24-02-20	19-09-18	14-10-20	15-12-20	07-05-19		0	0	0	1	0	0	0	0	0	0		Thu	Mon	Tue	Fri	Tue	Mon	Wed	Wed	Tue	Tue
51	15-10-20	07-02-19	07-10-19	08-03-18	13-07-21	29-04-20	12-09-18	05-08-18	08-04-18	20-03-20		0	0	0	0	0	0	0	1	1	1		Thu	Thu	Mon	Thu	Tue	Wed	Wed	Sun	Sun	Fri
52
CP dates

Cell Formulas
Range	Formula
L2,W41,L41,W28,L28,W15,L15,W2	L2	=FORMULATEXT(L3)
L3:U12	L3	=CP(A3:J12,">=1-1-21")
W3:AF12	W3	=CP(A3:J12,">=1/Jan/21")
L16:U25	L16	=CP(YEAR(A16:J25),">=2021")
W16:AF25	W16	=DAY(A29:J38)
L29:U38	L29	=CP(MONTH(A29:J38),"<=6")
W29:AF38	W29	=CP(DAY(A29:J38),">20")
L42:U51	L42	=CP(WEEKDAY(A42:J51,2),">=5")
W42:AF51	W42	=TEXT(A42:J51,"ddd")
Dynamic array formulas.

 

[Xlambda]

AIF.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
1	CP Part 5. Strings and wildcard chars behavior, SEARCH/FIND functionality,* triggers SEARCH, "!" triggers FIND, use of tilde (~) char
2
3			=CP(A4:A8,"a")				=--(A4:A8="a")				=CP(A4:A8,"A")				=--(A4:A8="A")
4	a		1				1				1				1
5	Ab		0				0				0				0
6	abc		0				0				0				0
7	Abcd		0				0				0				0
8	A		1				1				1				1
9
10			=CP(A11:A15,"*a")				=SEARCH("a",A11:A15)					=CP(A11:A15,"*A")				=SEARCH("A",A11:A15)
11	a		1				1					1				1
12	Ab		1				1					1				1
13	abc		1				1					1				1
14	Abcd		1				1					1				1
15	A		1				1					1				1
16
17			=CP(A18:A22,"!a")				=--ISNUMBER(FIND("a",A18:A22))							=CP(A18:A22,"!A")				=--ISNUMBER(FIND("A",A18:A22))
18	a		1				1							0				0
19	Ab		0				0							1				1
20	abc		1				1							0				0
21	Abcd		0				0							1				1
22	A		0				0							1				1
23
24			=CP(A25:A35,"*ab?")					=--ISNUMBER(SEARCH("ab?",A25:A35))								=CP(A25:A35,"!AB?")					=--(ISNUMBER(FIND("AB?",A25:A35)))
25	ab		0					0								0					0
26	Ab		0					0								0					0
27	AB		0					0								0					0
28	aB		0					0								0					0
29	abc		1					1								0					0
30	aBc		1					1								0					0
31	ABc		1					1								0					0
32	abcd		1					1								0					0
33	Abcd		1					1								0					0
34	aBcd		1					1								0					0
35	ABcd		1					1								0					0
36
37																Imp note: FIND does not work with wildcard chars
38
39			=CP(A40:A47,"*?ab")					=--ISNUMBER(SEARCH("?ab",A40:A47))								=CP(A40:A47,"*?ab?")					=--ISNUMBER(SEARCH("?ab?",A40:A47))
40	ab		0					0								0					0
41	cab		1					1								0					0
42	Cab		1					1								0					0
43	cAbC		1					1								1					1
44	CaBc		1					1								1					1
45	CABC		1					1								1					1
46	AB		0					0								0					0
47	aB		0					0								0					0
48
49			=CP(A50:A54,"*~*a")					=--ISNUMBER(SEARCH("~*a",A50:A54))								=CP(A50:A54,"*~?a")					=--ISNUMBER(SEARCH("~?a",A50:A54))
50	a		0					0								0					0
51	*a*		1					1								0					0
52	*a		1					1								0					0
53	?a		0					0								1					1
54	?a?		0					0								1					1
55
56			=CP(A57:A61,"*~*a?")					=--ISNUMBER(SEARCH("~*a?",A57:A61))								=CP(A57:A61,"*~?a?")					=--ISNUMBER(SEARCH("~?a?",A57:A61))
57	a		0					0								0					0
58	*a*		1					1								0					0
59	*a		0					0								0					0
60	?a		0					0								0					0
61	?a?		0					0								1					1
62
63			=CP(A64:A70,"*~*a~*")					=--ISNUMBER(SEARCH("~*a~*",A64:A70))								=CP(A64:A70,"*~?a~?")					=--ISNUMBER(SEARCH("~?a~?",A64:A70))
64	a		0					0								0					0
65	*a*		1					1								0					0
66	*a		0					0								0					0
67	*ab		0					0								0					0
68	?a		0					0								0					0
69	?a?		0					0								1					1
70	?ab		0					0								0					0
71
CP wild cards

Cell Formulas
Range	Formula
C3,G3,C63,H63,P63,U63,C56,H56,P56,U56,C49,H49,P49,U49,C39,H39,P39,U39,C24,H24,P24,U24,C17,G17,N17,R17,C10,G10,L10,P10,K3,O3	C3	=FORMULATEXT(C4)
C4:C8	C4	=CP(A4:A8,"a")
G4:G8	G4	=--(A4:A8="a")
K4:K8	K4	=CP(A4:A8,"A")
O4:O8	O4	=--(A4:A8="A")
C11:C15	C11	=CP(A11:A15,"*a")
G11:G15	G11	=SEARCH("a",A11:A15)
L11:L15	L11	=CP(A11:A15,"*A")
P11:P15	P11	=SEARCH("A",A11:A15)
C18:C22	C18	=CP(A18:A22,"!a")
G18:G22	G18	=--ISNUMBER(FIND("a",A18:A22))
N18:N22	N18	=CP(A18:A22,"!A")
R18:R22	R18	=--ISNUMBER(FIND("A",A18:A22))
C25:C35	C25	=CP(A25:A35,"*ab?")
H25:H35	H25	=--ISNUMBER(SEARCH("ab?",A25:A35))
P25:P35	P25	=CP(A25:A35,"!AB?")
U25:U35	U25	=--(ISNUMBER(FIND("AB?",A25:A35)))
C40:C47	C40	=CP(A40:A47,"*?ab")
H40:H47	H40	=--ISNUMBER(SEARCH("?ab",A40:A47))
P40:P47	P40	=CP(A40:A47,"*?ab?")
U40:U47	U40	=--ISNUMBER(SEARCH("?ab?",A40:A47))
C50:C54	C50	=CP(A50:A54,"*~*a")
H50:H54	H50	=--ISNUMBER(SEARCH("~*a",A50:A54))
P50:P54	P50	=CP(A50:A54,"*~?a")
U50:U54	U50	=--ISNUMBER(SEARCH("~?a",A50:A54))
C57:C61	C57	=CP(A57:A61,"*~*a?")
H57:H61	H57	=--ISNUMBER(SEARCH("~*a?",A57:A61))
P57:P61	P57	=CP(A57:A61,"*~?a?")
U57:U61	U57	=--ISNUMBER(SEARCH("~?a?",A57:A61))
C64:C70	C64	=CP(A64:A70,"*~*a~*")
H64:H70	H64	=--ISNUMBER(SEARCH("~*a~*",A64:A70))
P64:P70	P64	=CP(A64:A70,"*~?a~?")
U64:U70	U64	=--ISNUMBER(SEARCH("~?a~?",A64:A70))
Dynamic array formulas.

 

[Xlambda]

AIF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	AIF. Concept
2
3	If we have an array and 2 criteria in boolean OR (adding) relation, will get a possible array of 0,1, or 2's
4	2's - when both conditions are true (TRUE,TRUE)
5	1's - when only one of the two condition is true (TRUE,FALSE or FALSE,TRUE)
6	0's - if none of the conditions is true. (FALSE, FALSE)
7	To change the criteria relation into boolean AND (multiplication) we can use the same OR calculation with an extra condition.
8	When the result delivered with criteria in OR relation, is equal with total nr of criteria (2 in our case), means that will get the result for criteria like if they were in AND relation.
9	That's why AIF uses only one REDUCE lambda helper function, in OR relation for both scenarios:
10
11		….x,REDUCE(0,SEQUENCE(r),LAMBDA(v,i,v+CP(a,INDEX(f,i))))…...
12
13		criteria 1	>=-2												AIF(a,e,[o])
14		criteria 2	<=2		1st criteria		2nd criteria		OR relation		AND relation		check		o,omitted => AND relation
15	a		e		=A16:A22>=-2		=A16:A22<=2		=E16#+G16#		=IF(I16#=2,1,0)		=E16#*G16#		=AIF(A16:A22,C13:C14)
16	-3				FALSE		TRUE		1		0		0		0
17	-2				TRUE		TRUE		2		1		1		1
18	-1				TRUE		TRUE		2		1		1		1
19	0				TRUE		TRUE		2		1		1		1
20	1				TRUE		TRUE		2		1		1		1
21	2				TRUE		TRUE		2		1		1		1
22	3				TRUE		FALSE		1		0		0		0
23
24									=CP(A16:A22,C13)+CP(A16:A22,C14)				=CP(A16:A22,C13)*CP(A16:A22,C14)
25									1				0
26									2				1
27									2				1
28									2				1
29									2				1
30									2				1
31									1				0
32
Sheet4

Cell Formulas
Range	Formula
E15,G15,I15,K15,M15,O15,I24,M24	E15	=FORMULATEXT(E16)
E16:E22	E16	=A16:A22>=-2
G16:G22	G16	=A16:A22<=2
I16:I22	I16	=E16#+G16#
K16:K22	K16	=IF(I16#=2,1,0)
M16:M22	M16	=E16#*G16#
O16:O22	O16	=AIF(A16:A22,C13:C14)
I25:I31	I25	=CP(A16:A22,C13)+CP(A16:A22,C14)
M25:M31	M25	=CP(A16:A22,C13)*CP(A16:A22,C14)
Dynamic array formulas.

 

[Xlambda]

AIF.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	AE	AF
1	AIF. The function
2	=LAMBDA(a,e,[o],LET(f,AFLAT(e),r,ROWS(f),IF(r=1,CP(a,e),LET(x,REDUCE(0,SEQUENCE(r),LAMBDA(v,i,v+CP(a,INDEX(f,i)))),IF(o,IF(x,1,0),IF(x=r,1,0))))))
3
4	…f,AFLAT(e)…Chose to use AFLAT for the "e" expression criteria argument array , because can flatten any array, constant explicit array, of any shape or dimension, with or without blanks.
5
6									criteria OR relation				o,1
7		a							e				=AIF(B8#,I8:K11,1)
8		-18	-17	-16	-15	-14	-13		<=-16	-9	-5		1	1	1	0	0	0
9		-12	-11	-10	-9	-8	-7		0		5		0	0	0	1	0	0
10		-6	-5	-4	-3	-2	-1			>14	-2		0	1	0	0	1	0
11		0	1	2	3	4	5		7		3		1	0	0	1	0	1
12		6	7	8	9	10	11						0	1	0	0	0	0
13		12	13	14	15	16	17						0	0	0	1	1	1
14
15	...r,ROWS(f)….r, rows nr. represents nr. of criteria we have to deal with
16
17	...IF(r=1,CP(a,e),LET(…If we deal only with 1 criteria (r=1) function exits with calculation of CP(a,e), no need to get into REDUCE and last part calculations
18
19	...LET(x,REDUCE(0,SEQUENCE(r),LAMBDA(v,i,v+CP(a,INDEX(f,i))))….
20	REDUCE part, covered in previous post
21
22	….IF(o,IF(x,1,0),IF(x=r,1,0))…..last part, based on "o" value, keeps the result of REDUCE, that delivers OR relation if o=1, or changes it into an AND relation , if o=0 or omitted
23	o, operation argument
24	x, what REDUCE returns (OR relation only)
25	r, nr.criteria
26	1. if o<>0 (1) (OR operation) => IF(x,1,0) since x (REDUCE) does only OR relation (adding), x can be an array with 0's 1's, 2's...etc. This parts converts positive nrs. into 1's
27	2. if o=0 (AND operation) => IF(x=r,1,0) this part converts OR relation to AND relation, only values=nr. criteria are changed into 1's, rest of values will be changed into 0's
28
29	Task: Filter sample for strings than contain letter"a" or letter "b"																				Task: Filter for strings that have both letters "a" and "b"
30
31	a		=CP(A32:A39,"*a")+CP(A32:A39,"*b")							=AIF(A32:A39,{"*a","*b"},1)					=FILTER(A32:A39,J32#)						a		=AIF(A32:A39,{"*a","*b"})
32	c		0							0					ax						c		0
33	ax		1							1					ab						ax		0
34	ab		2							1					aB						ab		1			=FILTER(A32:A39,W32#)
35	aB		2							1					ba						aB		1			ab
36	ba		2							1					bx						ba		1			aB
37	bx		1							1					xb						bx		0			ba
38	xb		1							1											xb		0
39	d		0							0											d		0
40
41			Note: As we see above AIF converts 1's and 2's into 1's
42			FILTER include argument cares only for positive or negative nr.
43
AIF post 2

Cell Formulas
Range	Formula
M7,Z34,W31,C31,J31,O31	M7	=FORMULATEXT(M8)
B8:G13	B8	=SEQUENCE(6,6,-18)
M8:R13	M8	=AIF(B8#,I8:K11,1)
C32:C39	C32	=CP(A32:A39,"*a")+CP(A32:A39,"*b")
J32:J39	J32	=AIF(A32:A39,{"*a","*b"},1)
O32:O37	O32	=FILTER(A32:A39,J32#)
W32:W39	W32	=AIF(A32:A39,{"*a","*b"})
Z35:Z37	Z35	=FILTER(A32:A39,W32#)
Dynamic array formulas.

 

[Xlambda]

AIF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	CP. ComParison function
2	=LAMBDA(a,e,LET(x,LEFT(e,2),y,LEFT(e,1),z,SWITCH(x,"<=",x,">=",x,"<>",x,SWITCH(y,"<",y,">",y,"*",y,"!",y,"=")),w,SUBSTITUTE(e,z,"",1), v,IFERROR(--w,w),--SWITCH(z,"<>",a<>v,"<=",a<=v,">=",a>=v,"<",a<v,">",a>v,"*",ISNUMBER(SEARCH(v,a)),"!",ISNUMBER(FIND(v,a)),"=",a=v)))
3
4	1.-first part….x,LEFT(e,2),y,LEFT(e,1),z,SWITCH(x,"<=",x,">=",x,"<>",x,SWITCH(y,"<",y,">",y,"*",y,"!",y,"=")),w,SUBSTITUTE(e,z,"",1)….
5	This part is responsible for splitting all elements of "e" in an array of operators(z) and an array of values (w)
6	Has 2 LEFT (x,y), a nested SWITCH(…SWITCH(...)) (z) and a SUBSTITUTE (w) functions
7	Comparison operators can have double chars ( <=, >=, <>) single chars (<, >) and the "silent" "="
8	Plus our customs ones "*" to trigger SEARCH, and "!" to trigger FIND
9	First part of SWITCH checks if first 2 chars of "e" (x,LEFT(e,2) ) are any of double ones. If yes, exits with "x" value, (z=x)
10	if not,2nd SWITCH, checks if first char of "e", (y,LEFT(1) ) is any of single op. or custom ones, if yes, exits with "y" value (z=y)
11	if not (none of double or single operators are found) z will return "=" (z="=")
12	Note: I could have used for "z" :...z,IF(OR(x="<=",x=">=",x="<>"),x,IF(OR(y="<",y=">",y="*",y="!"),y,"="))
13	Looks that does same thing, but the problem is, OR delivers only a single result, can not spill, and I wanted z to be able to spill. SWITCH spills.
14	Now, "z" is created, we need the "values" array (w) , these will be whatever is left of "e" if we remove the operators array.
15	…w,SUBSTITUTE(e,z,"",1)…Here, the final 1 is important, we substitute with "" only the First instance of "z" found in "e"
16
17	"e" example:																=SUBSTITUTE(A19:A25,O19:O25,"",1)
18	e		x		y		1st SWITCH		2nd SWITCH			exit nested SWITCH			z		w
19	>=-3		>=		>		>=								>=		-3
20	abcd		ab		a							=			=		abcd
21	<25		<2		<				<						<		25
22	<>100		<>		<		<>								<>		100
23	89		89		8							=			=		89
24	**ab		**		*				*						*		*ab	(only 1st instance "removed")
25	!!abc		!!		!				!						!		!abc	(only 1st instance "removed")
26															operators		values(bcs of SUBSTITUTE,all in text format)
27
28	2. middle part …v,IFERROR(--w,w)…"w", delivers text, "v" changes text numeric values into numeric values, including dates in proper text format
29
30	3. last part..--SWITCH(z,"<>",a<>v,"<=",a<=v,">=",a>=v,"<",a<v,">",a>v,"*",ISNUMBER(SEARCH(v,a)),"!",ISNUMBER(FIND(v,a)),"=",a=v)
31	Nothing to say here, very straight forward, another SWITCH that triggers the corresponded operation for each operator
32
CP function

Cell Formulas
Range	Formula
Q17	Q17	=FORMULATEXT(Q19)
C19:C25	C19	=LEFT(A19:A25,2)
E19:E25	E19	=LEFT(A19:A25,1)
Q19:Q25	Q19	=SUBSTITUTE(A19:A25,O19:O25,"",1)
Dynamic array formulas.

 

[Xlambda]

AIF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Examples 1. AIF with comparison operators <,>,<=,>=,<>,=
2															kind of 2D filter
3	a						AND relation		=AIF(A4#,G4:G7)						=IF(I4#,A4#,"")
4	1	2	3	4	5		>=7	e	0	0	0	0	0
5	6	7	8	9	10		<=14		0	1	1	1	0			7	8	9
6	11	12	13	14	15		<>10		0	1	1	1	0			12	13	14
7	16	17	18	19	20		<>11		0	0	0	0	0
8
9	a						OR relation		=AIF(A10#,G10:G15,1)						=IF(I10#,A10#,"")
10	1	2	3	4	5		<=3	e	1	1	1	0	1		1	2	3		5
11	6	7	8	9	10		>=18		0	0	0	1	0					9
12	11	12	13	14	15		5		0	1	0	0	0			12
13	16	17	18	19	20		16		1	0	1	1	1		16		18	19	20
14							9
15							12
16									OR(e1)+AND(e2)
17	a						OR		=AIF(A18:E21,G18:G19,1)+AIF(A18:E21,G22:G23)
18	1	2	3	4	5		<3	e1	1	1	0	0	0		1	2
19	6	7	8	9	10		>18		0	0	1	1	1				8	9	10
20	11	12	13	14	15				1	1	1	0	0		11	12	13
21	16	17	18	19	20		AND		0	0	0	1	1					19	20
22							>7	e2							=IF(I18#,A18:E21,"")
23							<14
24
25	Note: regarding comparison operator "="
26	AIF was designed to trigger "=" operation whenever none of the other operators are found
27	Also, it will not be a problem if the "=" operator is written explicitly
28
29	a						OR relation		=AIF(A30#,G30:G35,1)						=IF(I30#,A30#,"")
30	1	2	3	4	5		<=3	e	1	1	1	0	1		1	2	3		5
31	6	7	8	9	10		>=18		0	0	0	1	0					9
32	11	12	13	14	15		5		0	1	0	0	0			12
33	16	17	18	19	20		=16		1	0	1	1	1		16		18	19	20
34							9
35							=12
36
Examples 1

Cell Formulas
Range	Formula
I3,O29,I29,I17,O9,I9,O3	I3	=FORMULATEXT(I4)
A4:E7,A30:E33,A10:E13	A4	=SEQUENCE(4,5)
I4:M7	I4	=AIF(A4#,G4:G7)
O4:S7,O30:S33,O10:S13	O4	=IF(I4#,A4#,"")
I10:M13,I30:M33	I10	=AIF(A10#,G10:G15,1)
I18:M21	I18	=AIF(A18:E21,G18:G19,1)+AIF(A18:E21,G22:G23)
O18:S21	O18	=IF(I18#,A18:E21,"")
O22	O22	=FORMULATEXT(O18)
Dynamic array formulas.

 

[Xlambda]

AIF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Filter by a List of items		Data, courtesy of Mike Girvin, ExcelIsFun
2						Desired Records:								=AREPORT(I4#,,Dp[#Headers])
3	Dep	OrderNo	Cat	Amount		Cat			=FILTER(Dp,AIF(Dp[Cat],F4:F11,1))					Dep	OrderNo	Cat	Amount
4	Legal	6525	6611	$1,265.23		2101			Finance	6549	7121	$4,874.14		Finance	6549	7121	$4,874.14
5	Operations	6526	6571	$3,868.73		4301			Operations	6572	4301	$4,417.44		Operations	6572	4301	$4,417.44
6	Finance	6527	2111	$2,015.30		7001			Maintenance	6573	2101	$3,719.51		Maintenance	6573	2101	$3,719.51
7	Human Resources	6528	3211	$4,283.20		3251			Maintenance	6576	6801	$2,619.46		Maintenance	6576	6801	$2,619.46
8	Legal	6529	1021	$2,949.47		7061			Human Resources	6591	2101	$206.19		Human Resources	6591	2101	$206.19
9	Finance	6530	6571	$4,496.66		7121			Human Resources	6601	6801	$190.64		Human Resources	6601	6801	$190.64
10	Human Resources	6531	2131	$2,238.96		4271			Human Resources	6625	4301	$393.94		Human Resources	6625	4301	$393.94
11	Human Resources	6532	6891	$1,814.54		6801			Accounting	6663	7061	$462.54		Accounting	6663	7061	$462.54
12	Finance	6533	6481	$3,849.74
13	Human Resources	6534	6841	$3,342.17										=AREPORT(I15#,,Dp[#Headers])
14	Accounting	6535	7051	$3,509.01		Dep	Amount		=FILTER(Dp,AIF(Dp[Dep],F15:F17,1)*AIF(Dp[Amount],G15:G16))					Dep	OrderNo	Cat	Amount
15	Operations	6536	6991	$3,480.03		Legal	>=1000		Legal	6525	6611	$1,265.23		Legal	6525	6611	$1,265.23
16	Maintenance	6537	6551	$3,617.99		Finance	<=2000		Human Resources	6532	6891	$1,814.54		Human Resources	6532	6891	$1,814.54
17	Legal	6538	3241	$349.00		Human Resources			Human Resources	6547	6561	$1,224.23		Human Resources	6547	6561	$1,224.23
18	Human Resources	6539	6521	$141.95					Legal	6563	6691	$1,089.11		Legal	6563	6691	$1,089.11
19	Maintenance	6540	6601	$4,230.50					Finance	6570	6581	$1,537.84		Finance	6570	6581	$1,537.84
20	Finance	6541	6591	$4,985.17					Human Resources	6594	4361	$1,582.50		Human Resources	6594	4361	$1,582.50
21	Human Resources	6542	6501	$4,585.62		other functions			Legal	6606	6861	$1,390.73		Legal	6606	6861	$1,390.73
22	Finance	6543	6971	$4,405.14		AREPORT			Human Resources	6617	5391	$1,734.93		Human Resources	6617	5391	$1,734.93
23	Operations	6544	4321	$653.37					Human Resources	6641	6501	$1,809.08		Human Resources	6641	6501	$1,809.08
24	Maintenance	6545	6611	$4,138.50					Finance	6643	6891	$1,199.69		Finance	6643	6891	$1,199.69
25	Accounting	6546	6581	$2,422.38					Human Resources	6658	6521	$1,773.56		Human Resources	6658	6521	$1,773.56
26	Human Resources	6547	6561	$1,224.23					Legal	6670	6811	$1,791.55		Legal	6670	6811	$1,791.55
27	Maintenance	6548	6941	$4,259.00					Human Resources	6671	6871	$1,268.30		Human Resources	6671	6871	$1,268.30
28	Finance	6549	7121	$4,874.14					Finance	6672	2111	$1,817.58		Finance	6672	2111	$1,817.58
29	Accounting	6550	7211	$759.52					Legal	6673	2131	$1,143.45		Legal	6673	2131	$1,143.45
30	Maintenance	6551	3211	$4,147.95
31	Human Resources	6552	2121	$2,709.42					Using AIF to filter horizontaly
32	Maintenance	6553	6701	$3,181.79					=AIF(Dp[#Headers],F14:G14,1)
33	Accounting	6554	6881	$2,540.32					1	0	0	1
34	Finance	6555	3201	$2,503.21
35	Human Resources	6556	7071	$3,315.10					=FILTER(N14#,AIF(Dp[#Headers],F14:G14,1))
36	Maintenance	6557	3191	$978.88					Dep	Amount
37	Accounting	6558	5381	$1,608.27					Legal	$1,265.23
38	Human Resources	6559	4321	$2,634.92					Human Resources	$1,814.54
39	Finance	6560	6951	$245.76					Human Resources	$1,224.23
40	Maintenance	6561	6661	$934.88					Legal	$1,089.11
41	Accounting	6562	6671	$818.15					Finance	$1,537.84
42	Legal	6563	6691	$1,089.11					Human Resources	$1,582.50
43	Accounting	6564	7181	$3,077.98					Legal	$1,390.73
44	Human Resources	6565	7101	$4,885.00					Human Resources	$1,734.93
45	Maintenance	6566	6481	$1,279.55					Human Resources	$1,809.08
46	Operations	6567	5391	$1,982.58					Finance	$1,199.69
47	Accounting	6568	4261	$4,998.69					Human Resources	$1,773.56
48	Operations	6569	7101	$1,695.86					Legal	$1,791.55
49	Finance	6570	6581	$1,537.84					Human Resources	$1,268.30
50	Accounting	6571	7141	$3,156.93					Finance	$1,817.58
51	Operations	6572	4301	$4,417.44					Legal	$1,143.45
52	Maintenance	6573	2101	$3,719.51
53	Finance	6574	4351	$3,590.71
54	Maintenance	6575	7151	$3,140.17
55	Maintenance	6576	6801	$2,619.46
56	Accounting	6577	6671	$2,246.14
57	Operations	6578	3231	$4,029.57
58	Accounting	6579	3201	$1,321.97
59	Operations	6580	6751	$3,026.18
60	Operations	6581	6601	$3,419.71
61	Legal	6582	6911	$776.53
62	Operations	6583	6681	$1,768.00
63	Maintenance	6584	6701	$1,148.60
64	Operations	6585	6771	$265.40
65	Legal	6586	5411	$367.18
66	Operations	6587	6561	$1,061.33
67	Human Resources	6588	6821	$4,500.55
68	Accounting	6589	1001	$942.39
69	Maintenance	6590	6731	$4,476.57
70	Human Resources	6591	2101	$206.19
71	Accounting	6592	6951	$326.85
72	Maintenance	6593	4281	$4,172.97
73	Human Resources	6594	4361	$1,582.50
74	Operations	6595	4361	$3,929.85
75	Human Resources	6596	3171	$955.69
76	Operations	6597	7031	$777.09
77	Maintenance	6598	6501	$2,904.57
78	Finance	6599	3221	$788.51
79	Finance	6600	6551	$434.96
80	Human Resources	6601	6801	$190.64
81	Legal	6602	6641	$3,734.56
82	Maintenance	6603	3221	$937.29
83	Maintenance	6604	6981	$2,517.09
84	Finance	6605	7091	$2,573.43
85	Legal	6606	6861	$1,390.73
86	Operations	6607	2131	$569.00
87	Operations	6608	2121	$919.67
88	Maintenance	6609	6941	$2,940.34
89	Legal	6610	6621	$2,220.63
90	Finance	6611	7051	$903.36
91	Operations	6612	6551	$1,243.96
92	Legal	6613	6791	$2,084.00
93	Finance	6614	3181	$516.86
94	Human Resources	6615	7161	$3,063.06
95	Human Resources	6616	5431	$2,770.24
96	Human Resources	6617	5391	$1,734.93
97	Operations	6618	6661	$4,755.49
98	Finance	6619	4281	$3,207.31
99	Finance	6620	6951	$638.38
100	Maintenance	6621	2131	$4,676.85
101	Maintenance	6622	6491	$1,696.54
102	Accounting	6623	6971	$3,744.77
103	Operations	6624	6761	$3,805.21
104	Human Resources	6625	4301	$393.94
105	Accounting	6626	2111	$502.53
106	Accounting	6627	1021	$2,764.06
107	Operations	6628	5421	$4,276.44
108	Maintenance	6629	5391	$438.72
109	Maintenance	6630	7181	$2,948.21
110	Operations	6631	6721	$369.69
111	Legal	6632	3231	$417.73
112	Maintenance	6633	7071	$4,537.48
113	Maintenance	6634	4351	$1,646.13
114	Finance	6635	7211	$4,705.00
115	Finance	6636	6621	$4,531.84
116	Maintenance	6637	7211	$3,560.45
117	Legal	6638	5381	$584.22
118	Maintenance	6639	6751	$429.12
119	Accounting	6640	6691	$102.79
120	Human Resources	6641	6501	$1,809.08
121	Maintenance	6642	7131	$4,066.42
122	Finance	6643	6891	$1,199.69
123	Legal	6644	6461	$3,956.52
124	Maintenance	6645	7071	$3,052.45
125	Accounting	6646	5371	$202.18
126	Accounting	6647	6871	$3,952.80
127	Accounting	6648	4311	$4,192.83
128	Accounting	6649	6871	$2,772.62
129	Human Resources	6650	7051	$965.17
130	Human Resources	6651	2131	$3,270.53
131	Maintenance	6652	7011	$1,932.48
132	Finance	6653	6841	$547.82
133	Finance	6654	6991	$2,185.79
134	Operations	6655	5381	$1,476.24
135	Human Resources	6656	7171	$2,851.43
136	Operations	6657	3211	$2,788.30
137	Human Resources	6658	6521	$1,773.56
138	Maintenance	6659	7111	$2,161.50
139	Finance	6660	7161	$2,979.32
140	Legal	6661	7111	$2,377.74
141	Human Resources	6662	2121	$731.41
142	Accounting	6663	7061	$462.54
143	Maintenance	6664	6841	$1,713.13
144	Human Resources	6665	6711	$552.88
145	Operations	6666	6601	$1,282.53
146	Maintenance	6667	6451	$3,444.59
147	Finance	6668	6991	$526.92
148	Operations	6669	4291	$4,239.64
149	Legal	6670	6811	$1,791.55
150	Human Resources	6671	6871	$1,268.30
151	Finance	6672	2111	$1,817.58
152	Legal	6673	2131	$1,143.45
153	Operations	6674	2121	$4,278.78
154	Accounting	6675	6571	$1,104.68
155	Legal	6676	3151	$4,089.90
156	Operations	6677	6631	$250.38
157	Operations	6678	6771	$221.97
158	Legal	6679	6691	$3,588.85
159	Finance	6680	3191	$3,396.70
160
Examples 2

Cell Formulas
Range	Formula
N2,I35,I32,I14,N13,I3	N2	=FORMULATEXT(N3)
N3:Q11,N14:Q29	N3	=AREPORT(I4#,,Dp[#Headers])
I4:L11	I4	=FILTER(Dp,AIF(Dp[Cat],F4:F11,1))
I15:L29	I15	=FILTER(Dp,AIF(Dp[Dep],F15:F17,1)*AIF(Dp[Amount],G15:G16))
I33:L33	I33	=AIF(Dp[#Headers],F14:G14,1)
I36:J51	I36	=FILTER(N14#,AIF(Dp[#Headers],F14:G14,1))
Dynamic array formulas.