Xlookup+FILTER match & Spill Data

[paradise]

I want to get data by range lookup and return in array.
Something like below.
Xlookup (A5:A979,Data!C7:C1120,Data!A7:A1120+Data!B7:B1120+Data!D7:D1120+Data!L7:L1120+Data!N7:O1120)

 

[Peter Bartholomew]

As it stands, the formula should return an arithmetic sum of columns A, B, D, L and N on the data sheet. If you wish to return content drawn from each column, different strategy is required. Due to an unfortunate limitation (some would call it an error) in the specification of the new array functionality, XLOOKUP does not return 2D arrays. It will return a row or a column but not a list of rows. For that you will need INDEX and XMATCH.
The row and column numbers would be given by

= XMATCH(A5:A979,Data!C7:C1120)

= {1,2,4,12,14}

respectively.
The final formula would be

= INDEX(Array, rowNumbers, columnNumbers)

or, at least, an equivalent pile of direct cell references.

 

[paradise]

Ok.There is another that tried but something is wrong.

=LET(z,LAX!C6:R972,l,A3:A1752,FILTER(FILTER(SORTBY(z,MATCH(INDEX(z,,16),l,)),SEQUENCE(ROWS(z))<=ROWS(l)),{0,0,1,0,0,0,0,0,1,0,0,0,0,0,1,1}))

 

[Peter Bartholomew]

Could you provide a smallish workbook of test data and a statement of the result you wish to achieve? In principle there is no reason why bidirectional use of FILTER should not work. If you have insider beta version of Excel, CHOOSECOLS might also be useful.

 

[paradise]

No,I don't have insider beta version.
Plz find attachment.

 

[Peter Bartholomew]

I think a simple INDEX/XMATCH works best.

= LET(
  rowIndex,  XMATCH(lookupValues,lookupRange),
  columnIdx, XMATCH(headerValues,headers),
  result,    INDEX(data, rowIndex, columnIdx),
  IFNA(result,0))

 

[paradise]

I want simply to match column with another worksheet and get the multiple columns specified.I don't like to match row header.I have mentioned header just to ease the query.

 

[Peter Bartholomew]

That is no problem. All you have to do is select the line that calculates the column indices and hit F9 to get a hard-coded array.

= LET(
  rowIndex,  XMATCH(lookupValues,lookupRange),
  columnIdx, {1,4,10,16,17},
  result,    INDEX(data, rowIndex, columnIdx),
  IFNA(result,0))

 

[paradise]

Ok.Can u figure out what is missing and restate Post#3,code.

 

[Peter Bartholomew]

Basically, the formula looks sound. I extended the range z to include the column with Extract5. I don't use direct cell referencing and couldn't see anything especially significant about cell A1752 so I adjusted the formula to the actual data but that is simply to enable me to work with it,
I finished with

= LET(
    z, data,
    ℓ, lookupValues,
    FILTER(
      FILTER(SORTBY(z,MATCH(INDEX(z,,2),ℓ,)),SEQUENCE(ROWS(z))<=ROWS(ℓ)),
      {1,0,0,1,0,0,0,0,0,1,0,0,0,0,0,1,1}
    )
  )

 

[paradise]

Thanks