ARRAYTOTEXT with CELL function doesn't work?

New 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?



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 Sorry! Misinterpreted your question. What Excel version are you on?


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



best response confirmed by CFYeap (New Contributor)

Hi @CFYeap 


Alternatively, in 'generate list'!D6:

    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


As variant

      XLOOKUP(C2, Table1[Date], Table2[Event]):
      XLOOKUP(C3, Table1[Date], Table2[Event]))