This Excel LAMBDA function recursively applies filter criteria to an array or range

excel-lambda-RECURSIVEFILTER.txt

/*
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
    )
  )
);