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,

You current FILTER() formula should work what you expect. From which data you are getting 2019, 2020 & 2021? — Harun24hr
– Harun24hr, Commented Nov 7, 2023 at 2:52
Apologies if the pictures/my explanation there wasn't clear, the 2019, 2020, and 2021 highlighted in yellow are the values I would like to see based off of the values in the columns below each respective header, matched from the original data table. The Filter() function is indeed already working as expected, but I want the spilled array to have headers to mark where each column is from. The headers highlighted there in yellow were added manually by me to illustrate the result I was looking to achieve. — AesusV
– AesusV, Commented Nov 7, 2023 at 3:06
Would it be a problem to filter columns while headers are included? =FILTER($E$47:$I$59,(($E$47:$I$47>=2019)*($E$47:$I$47<=2021))) or filter headers separately using same conditions =FILTER($E$47:$I$47,(($E$47:$I$47>=2019)*($E$47:$I$47<=2021))) ? — user11222393
– user11222393, Commented Nov 7, 2023 at 5:42
Then your formula should work. Aren't they the year presentation of a date value? In that case use =FILTER($E$48:$I$59,(($E$47:$I$47>=date(2019,1,1)*($E$47:$I$47<date(2022,1,1)))) note the end date to be the next year and smaller than. — P.b
– P.b, Commented Nov 8, 2023 at 6:22
I think I understand what you mean now. You don't want to include the headers in the original formula, because the next formula refers to it, but you want the headers to be populated dynamically. I think the better option would be to move the formula up one cell and include the header as suggested by user11222393 and in the next formula dont refer to DROP(E61#,1) instead of referring to E62#. Else explain the purpose of not including the headers in your filter in the first place. — P.b
– P.b, Commented Nov 8, 2023 at 16:12

VBasic2008 · Accepted Answer · 2023-11-07 08:24:19Z

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)))

edited Nov 7, 2023 at 8:24

answered Nov 7, 2023 at 7:44

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

P.b · Accepted Answer · 2023-11-08 21:20:21Z

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)))))

Collectives™ on Stack Overflow

Excel, Matching an Array in an index function

2 Answers 2

Choose Columns Between Headers

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Choose Columns Between Headers

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related