Excel, Matching an Array in an index function

Question

The goal here is to have a reverse index match function return the column/row header based off of a resultant array that was generated from a filter function.

Original dataset (=RANDARRAY(12,5,1,100,TRUE))

Filtered Data Set =FILTER($E$48:$I$59,(($E$47:$I$47>=2019)*($E$47:$I$47<=2021)))

The highlighted column headers in the second picture are what I am trying to have be generated dynamically by matching the array that was returned from the filter function to the original array.

I was thinking an Index function might work, possibly with an additional index in the match function in order to handle the 2D array I'm trying to search via, but I'm unable to get it to work.

I've also tried an iferror find function which does (via spill) return 1's for the expected column but I'm struggling with how I might use this to identify it as the column "2019" without the use of a helper column (which I'm trying to avoid).

IFERROR(FIND())

Here's a clearer image of what I'm trying to achieve. Looking for the year headers from the original data table, using the 1D array that was generated via the 'filer()' function to match the correct column. This IndexMatch formula of course does not work, but it was a first attempt and illustrates the goal.

If anyone could offer some direction, advice, or guidance I'd be appreciative.

Thanks,

Answer 1

Choose Columns Between Headers

=LET(data,E47:I59,start,2019,end,2021,
    h,TAKE(data,1),
FILTER(data,(h>=start)*(h<=end)))

• I'm not sure what made me do this.

=LET(data,E47:I59,start,2019,end,2021,
    h,TAKE(data,1),
    ms,XMATCH(start,h),
    me,XMATCH(end,h)+1,
CHOOSECOLS(data,SEQUENCE(,me-ms,ms)))

Answer 2

If I understood the question correctly you're trying to lookup the values in a column of an array formula to entirely match a column in the main data.

As commented my advice would be to simply include the headers in the filter and when you need to refer to the numbers of the array excluding the headers, use DROP(array,1) so the first row is dropped.

But to answer your question:

You could do this using BYROW/MMULT by matching all single values in the column to match:

=BYCOL(E62#,LAMBDA(c,FILTER(E47:I47,MMULT(SEQUENCE(,ROWS(c),,0),N(c=E48:I59))=12)))

Or by using a double BYCOL/TEXTJOIN:

=LET(y,  E47:I47,
     cy, BYCOL(E48:I59,LAMBDA(c,TEXTJOIN(",",,c))),
BYCOL(E62#,LAMBDA(c,INDEX(y,XMATCH(TEXTJOIN(",",,c),cy)))))