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.
Actually in the expected results you didn't indicate that there is a scenario without a country code:
If the formula should recognise scenarios without a country code one would have to add a NOT(ISBLANK(sheet4!G7)) statement in the formula.
However you work with Office365 and therefore i suggest to apply the new functions which extremely simplify task like this.
OliverScheurich, thanks so much for the formula, it works perfectly for my dataset which actually is where a huge dataset. Anyways, as you said, I used the blank condition for it to not return any value when country is not specified.
Additionally before we close this topic, from the same formula, is it possible to return all the values concatenated together in one cell when there are multiple values as the below screenshot:
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.
=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.
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))You are welcome. With the same formula - which means without the Office365 functions such as TEXTJOIN, FILTER, UNIQUE - i have never seen or done a concatenation like this. Therefore I am glad that I can solve such tasks in Excel online.