Forum Discussion

CFYeap's avatar
CFYeap
Copper Contributor
Aug 02, 2022
Solved

ARRAYTOTEXT with CELL function doesn't work?

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. Plea...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Aug 03, 2022

    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)))
)
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 02, 2022

CFYeap Why not enter this in D6?

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

 

  • CFYeap's avatar
    CFYeap
    Copper Contributor
    Aug 03, 2022

    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.

     

     

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Aug 03, 2022

      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))

       

Resources