Having an issue with index formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2073220%22%20slang%3D%22en-US%22%3EHaving%20an%20issue%20with%20index%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2073220%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20this%20is%20my%20first%20post%20here!%20I'm%20struggling%20with%20the%20formula%20below.%20I'm%20trying%20to%20create%20a%20formula%20that%20joins%20multiple%20instances%20of%20something%20into%20a%20single%20string.%20The%20formula%20below%20does%20what%20I%20need%20it%20do%20but%20breaks%20when%20I%20run%20a%20script%20to%20update%20the%20worksheet%20(even%20though%20the%20cells%20referenced%20here%20are%20untouched).%20It%20seems%20like%20the%20formula%20resets%20to%20an%20array%20formula%2C%20but%20when%20I%20click%20on%20it%20and%20press%20it%20enter%2C%20it%20becomes%20a%20traditional%20formula%20that%20works%20without%20the%20'%7D'.%20Suggestions%3F%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%3B%20%22%2CTRUE%2C%20INDEX(%24R%2410%3A%24R%24999%2C%20IF(A2%3D%24O%2410%3A%24O%2419%2C%20ROW(%24O%2410%3A%24O%2419)-ROW(%24O%2410)%2B1%2C%20900)%2C1%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2073220%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2073248%22%20slang%3D%22en-US%22%3ERe%3A%20Having%20an%20issue%20with%20index%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2073248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F934117%22%20target%3D%22_blank%22%3E%40sjamm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20which%20version%20of%20Excel%20you%20are%3F%20If%20on%20one%20with%20dynamic%20arrays%20CSE%20is%20not%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello, this is my first post here! I'm struggling with the formula below. I'm trying to create a formula that joins multiple instances of something into a single string. The formula below does what I need it do but breaks when I run a script to update the worksheet (even though the cells referenced here are untouched). It seems like the formula resets to an array formula, but when I click on it and press it enter, it becomes a traditional formula that works without the '}'. Suggestions?

=TEXTJOIN("; ",TRUE, INDEX($R$10:$R$999, IF(A2=$O$10:$O$19, ROW($O$10:$O$19)-ROW($O$10)+1, 900),1,1))

1 Reply

@sjamm 

On which version of Excel you are? If on one with dynamic arrays CSE is not needed.