SOLVED

ARRAYTOTEXT with CELL function doesn't work?

Copper Contributor

Hi, I am trying to generate a list (text) from my data and I couldn't get the ARRAYTOTEXT to work. I am using Microsoft 365 version 2206 64 bit (15330.20264 click-to-run), running on Windows 10. Please find sample file here.

 

My aim is to generate a list (in this sample, a list of activities done) by selecting the range of travel dates. For example, if the travel date range from 2018-2020, the generated list should contain all the activities done within the duration. The only input should be "selecting the date range".

 

Since I am working with text, I used ARRAYTOTEXT to join the contents of the arrays. This is possible when the cell range is typed into the ARRAYTOTEXT formula manually, but it ends up with "there's a problem with the formula" when I try to refer the cell range from another cell (ie. D2, D3 in the excel sheet 'generate list'). I used CELL function combined with ARRAYTOTEXT.

 

The expected result should be as in D6. The problem I faced happened in C6. Does anyone have an idea what other function can I use other than these 2 to generate the list, or what can I do to make them work?

 

Thank you!

 

 

7 Replies

@CFYeap Why not enter this in D6?

=SUBSTITUTE(ARRAYTOTEXT(Table2[Event]),",",CHAR(10))

 

Hi @Riny_van_Eekelen,

 

Thanks for the suggestion. That will give a whole list of all events ever done (result from your formula in E6, versus the desired result in D6). I would like to display only those events happened within the range specified from date in C2 to C3.

 

CFYeap_0-1659508915076.png

 

@CFYeap Sorry! Misinterpreted your question. What Excel version are you on?

 

Edit: This one should work (not very pretty though):

=SUBSTITUTE(ARRAYTOTEXT(INDEX(Table2[Event],MATCH(C2,Table1[Date],0)):INDEX(Table2[Event],MATCH(C3,Table1[Date],0))),",",CHAR(10))

 

best response confirmed by CFYeap (Copper Contributor)
Solution

Hi @CFYeap 

 

Alternatively, in 'generate list'!D6:

=LET(
    From, XMATCH(C2,Table1[Date]),
    To,   XMATCH(C3,Table1[Date]),
    TEXTJOIN(CHAR(10),,INDEX(Table2[Event], SEQUENCE(To-From+1,,From)))
)
Wow, both @Riny_van_Eekelen and @L z. 's methods worked. They are simpler than the workaround I thought of later using concat().
Thank you both for the help!
@CFYeap. Glad we could help & Thanks for providing feedback

@CFYeap 

As variant

=TEXTJOIN( CHAR(10),,
      XLOOKUP(C2, Table1[Date], Table2[Event]):
      XLOOKUP(C3, Table1[Date], Table2[Event]))
1 best response

Accepted Solutions
best response confirmed by CFYeap (Copper Contributor)
Solution

Hi @CFYeap 

 

Alternatively, in 'generate list'!D6:

=LET(
    From, XMATCH(C2,Table1[Date]),
    To,   XMATCH(C3,Table1[Date]),
    TEXTJOIN(CHAR(10),,INDEX(Table2[Event], SEQUENCE(To-From+1,,From)))
)

View solution in original post