0

In Excel 365, how can I select non-contiguous columns from a range using a single dynamic array formula? Consider data in columns A:E as follows:

enter image description here

Entering =XLOOKUP(G1,A1:E1,A2:E11) into cell G2 returns the column labeled col2. But =XLOOKUP(G1:I1,A1:E1,A2:E11) returns the first entry of the those three columns instead of the entire columns. How can I understand this behavior? What's an alternative?

I can similarly retrieve a single column but not a group of columns with INDEX/XMATCH or FILTER:

=INDEX(A2:E11,0,XMATCH(G1,A1:E1))

=FILTER(A2:E11,A1:E1=G1)

Improve this question

1 Answer 1

Reset to default
1

Instead of 0 for the rows, use SEQUENCE to return an array of numbers to the INDEX:

=INDEX(A2:E11,SEQUENCE(ROWS(A2:E11)),MATCH(G1:I1,A1:E1,0))

enter image description here

Improve this answer
4
  • This works! Do you know why using a 0 instead of SEQUENCE() forces implicit intersection and returns a single row?
    – bkraines
    Commented Jul 29, 2020 at 22:21
  • @bkraines nope, I thought it should work.
    – Scott Craner
    Commented Jul 29, 2020 at 22:26
  • 1
    Apparently 0 doesn't work because "arrays of arrays" are not yet supported: youtube.com/watch?v=M6BbkjWGXNs
    – bkraines
    Commented Jul 30, 2020 at 15:42
  • Ahh, that would make sense. By using SEQUENCE we are creating a 2D array. But by using 0 it creates a 1 dimensional array of 3 1 dimensional arrays. Thanks for finding that.
    – Scott Craner
    Commented Jul 30, 2020 at 15:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .