Store/lookup list of values in a single table cell
Conceptually I would have two tables. The first table has a column where I call a lambda function. That function takes a category (from the same table) and it also takes a range of dates. The second table contains information about category 'A', including the list of dates in question. The list of dates may be of varying length. But a range (as in: a constant array {date1, date2} or SEQUENCE()) cannot be stored in a single table cell for lookup.
Table 1
| Category | Function |
|---|---|
| A | =F([@Category],DateLookup([@Category])) |
| B | =F([@Category],DateLookup([@Category])) |
DateLookup() here can be any lookup mechanism.
Table 2
| Category | Dates |
|---|---|
| A | 4/13/2026,5/13/2026 |
| B | 2/13/2026,3/13/2026,4/13/2026 |
Is storing the dates as strings and using TEXTSPLIT() really the only scalable way to go? Is there a 'proper' way which will allow for some lookup mechanism to pass the dates as a range to the lambda?
The only other way I've gotten this to work is to split out the Category/Dates to its own worksheet as dynamic arrays with VSTACK(HSTACK()), hard coding the dates in the formula, getting the range as such, filtering the #NA resulting from the jagged size. But this seems terrible.
=LET( dates, XLOOKUP([@Category], Sheet1!$A$2#,Sheet1!$B$2#), dates_trim, FILTER(dates,NOT(ISNA(dates))) ) [link] [comments]
Want to read more?
Check out the full article on the original site