• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Xlookup+FILTER match & Spill Data

paradise

Member
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

Well-Known Member
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
Code:
= XMATCH(A5:A979,Data!C7:C1120)

= {1,2,4,12,14}
respectively.
The final formula would be
Code:
= INDEX(Array, rowNumbers, columnNumbers)
or, at least, an equivalent pile of direct cell references.
 

paradise

Member
Ok.There is another that tried but something is wrong.
Code:
=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

Well-Known Member
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.
 

Peter Bartholomew

Well-Known Member
I think a simple INDEX/XMATCH works best.
Code:
= LET(
  rowIndex,  XMATCH(lookupValues,lookupRange),
  columnIdx, XMATCH(headerValues,headers),
  result,    INDEX(data, rowIndex, columnIdx),
  IFNA(result,0))
 

Attachments

paradise

Member
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

Well-Known Member
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.
Code:
= LET(
  rowIndex,  XMATCH(lookupValues,lookupRange),
  columnIdx, {1,4,10,16,17},
  result,    INDEX(data, rowIndex, columnIdx),
  IFNA(result,0))
 

Peter Bartholomew

Well-Known Member
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
Code:
= 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}
    )
  )
 

Attachments

Top