Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This Excel LAMBDA function recursively applies filter criteria to an array or range
/*
RECURSIVEFILTER
Filters an array or range recursively using a list of columns and criteria to apply
Inputs
- dat: the array or range of data to filter
- cols: Either a one-dimensional horizontal array of column indices representing
columns in dat. e.g. {1,2} means "filter columns 1 and 2"
OR
A one-dimensional horizontal array of column names, which are positioned exactly
one row above the data.
- crits: a one-dimensional horizontal array of filter criteria.
e.g. {"Filter1","Filter2"} will be applied to INDEX(cols,1,1)
and INDEX(cols,1,2) respectively
Returns
A filtered array.
Example
To use FILTER normally with 2 criteria joined with the AND operator, we must:
=FILTER(dat,(column1=criteria1)*(column2=criteria2))
Since this becomes complicated with lots of filters, RECURSIVEFILTER does this:
=FILTER(FILTER(dat,column1=criteria1),column2=criteria2)
and so on, until all filters are applied
*/
RECURSIVEFILTER = LAMBDA(dat,cols,crits,
IF(COLUMNS(cols)<>COLUMNS(crits),"RECURSIVEFILTER ERROR: number of columns must match number of filter values",
LET(
/*if any coltypes are TYPE=2 (text), then assume they are names*/
cols_are_names,OR(MAP(cols,LAMBDA(x,TYPE(x)))=2),
/*if cols are names, then find the positions of the columns using the row above the data*/
col_positions,IF(cols_are_names,XMATCH(cols,OFFSET(INDEX(dat,1,),-1,0)),cols),
/*get the first column position*/
thiscol,INDEX(dat,,INDEX(col_positions,1,1)),
/*get the first criteria*/
thiscrit,INDEX(crits,1,1),
/*apply the filter*/
filt,FILTER(dat,thiscol=thiscrit),
/*if the number of column positions is greater than 1, this isn't the last criteria to apply
so call the function again, passing the remaining criteria into the next call.
If the number of column positions is 1, this is the last criteria, so return the output*/
output,IF(
COLUMNS(col_positions)>1,
RECURSIVEFILTER(
filt,
INDEX(col_positions,,SEQUENCE(1,COLUMNS(col_positions)-1,2)),
INDEX(crits,,SEQUENCE(1,COLUMNS(crits)-1,2))
)
,filt
),
output
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment