Using Hash Operator with XLookup

[Srikanth Amudhan]

Can hash operator be used with Xlookup function? If yes, can a syntax example be provided? Objective: to execute Xlookup where lookup array, and consequently return array, can be dynamic. E.g.: no. of rows to lookup return could be 100, 200, 1000 etc.

 

[p45cal]

Save us some time by setting up an example workbook with sample data and what you're looking to do. A few sample results added manually by you so that we can see what you're trying to do would be very helpful - otherwise we're just guessing, and probably wrongly.

 

[Peter Bartholomew]

Yes, it works fine. You can have a dynamic array as the lookup range, the return range or even a set of lookup values.
Also if the returned value is itself the anchor cell of a dynamic range, adding "#" to the XLookup function call returns a copy of the spilt range.

= XLOOKUP(target, distinct#, total#, "")

= XLOOKUP(target, distinct#, sorted, "")#

 

[Srikanth Amudhan]

Save us some time by setting up an example workbook with sample data and what you're looking to do. A few sample results added manually by you so that we can see what you're trying to do would be very helpful - otherwise we're just guessing, and probably wrongly.

Hello,
Please see attached Excel file. Sheet2 tab mentions what the ask is. Desired Result tab should be self-explanatory, in conjunction with Sheet2 tab.

I hope this helps and suffices. Greatly appreciate the help and guidance!

 

[Peter Bartholomew]

When you mentions the "#" array notation I assumed you had calculated array data to look up. For source data the best way of identifying its extent is to use an Excel Table (unless you are using Excel 2003 or earlier). It is possible to lookup first and last rows using XLOOKUP but, by comparison with a Table it is an ugly way of working. See the following three approaches:

= LET(
  startCell, XLOOKUP(TRUE, ISNUMBER('Data Source1'!B:B), 'Data Source1'!B:F,,,1),
  endCell,   XLOOKUP(TRUE, ISNUMBER('Data Source1'!B:B), 'Data Source1'!B:F,,,-1),
  startCell:endCell)

= TAKE(Table1,,5)

= LET(
  r, SEQUENCE(ROWS(Table2)),
  c, XMATCH(headersReq,Table2[#Headers]),
  INDEX(Table2, r, c))

 

[p45cal]

@Srikanth Amudhan , what is the dynamic range you want to use as the first argument of XLOOKUP?