Forum Discussion
String Match part of an array with repeated strings in duplicate records and return value
=IF(SUM(N(IF((sheet3!$B$2:$B$7=sheet4!H7)*ISNUMBER(SEARCH(sheet4!G7,sheet3!$C$2:$C$7))=1,MATCH(IF((sheet3!$B$2:$B$7=sheet4!H7)*ISNUMBER(SEARCH(sheet4!G7,sheet3!$C$2:$C$7))=1,sheet3!$D$2:$D$7),IF((sheet3!$B$2:$B$7=sheet4!H7)*ISNUMBER(SEARCH(sheet4!G7,sheet3!$C$2:$C$7))=1,sheet3!$D$2:$D$7),0)=ROW($1:$6))))>1,"Multiple",INDEX(sheet3!D$2:D$7,MATCH(1,(sheet3!$B$2:$B$7=sheet4!H7)*ISNUMBER(SEARCH(sheet4!G7,sheet3!$C$2:$C$7)),0)))
This formula seems to work in my sheet. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office365 or 2021. Do you happen to work with Office365 or 2021? I think with the help of the UNIQUE and FILTER formula it would be a lot easier to do this.
OliverScheurich, from the formula, I tried only the below part to return all the values in one cell but it is only returning the first instance. How can tweak this to return all matching values in one cell without SPILL error so that I can concatenate them?
INDEX(dataArticleInformation!$AD:$AD;MATCH(1;(dataArticleInformation!$C:$C=$G12)*
ISNUMBER(SEARCH($D12;dataArticleInformation!$AK:$AK));0))
=TEXTJOIN("",,UNIQUE(FILTER($D$2:$D$7,($B$2:$B$7=A11)*ISNUMBER(SEARCH(B11,$C$2:$C$7)))))This formula works in my sheet.
Hello OliverScheurich , hope you are doing well! Following up the solution you provided for my problem which is working excellently. However, for some reason with this formula in place there is lot of performance issue due to calculation I have been seeing when I tried to enter a new value in any part of the cell and when I tried to troubleshoot I saw that the formula is dependent on all the columns of the sheet even when there is no formula written in those cells. I tried to investigate more but could not understand how this dependency is being created and I am sure this is what is causing the calculation everytime something is entered anywhere in the sheet.
OliverScheurich, thank so much!! This too works perfectly for me!! Thanks for all the help.