SOLVED

String Match part of an array with repeated strings in duplicate records and return value

Copper Contributor

Hi,

I have a dataset like below in Sheet3 as in the screenshot with ID, country and number where country column has 2 letter country codes as array of multiple countries together unique to a ID and number combination. You can also see that there are more than one record with the combination of ID and number due to local language name. 

Dataset.PNG

 

In Sheet2, I have a 2 letter country code dropdown and a ID column, where when I select a country and against the ID, in the column H4 I should be able to fetch a number value from that dataset I mentioned earlier or just return multiple if the country exists in more than one combination. 

Dropdown1.PNG

 

Dropdown.PNG

 

For that I have written the below formula in the column H4 using vlookup to find the country string in the helper column I created which is the concatenation of ID and country column string and cover the scenarios of the country existing in first place after the ID which is of fixed length or middle position or the last position to tell me if the country exists in one combination or in more than one. However, in a scenario like in the current dataset where the country "FI" or "SE" exists in more than one combination moreover in the same position due to which vlookup is returning the value for the first instance of the match rather it should say multiple. Is it possible to tweak my current formula to also include these scenarios or should I have to rethink of a new logic on the whole ?

 

Formula:

IF(AND($G4<>"";$F4<>"");
IFERROR(IF(OR(AND((IFERROR(IF(VLOOKUP($G4&$F4&"*?";Sheet3!A2:E7;4;FALSE)<>0;"TRUE";
"FALSE");"FALSE"));(IFERROR(IF(VLOOKUP($G4&"*?"&$F4&"*?";Sheet3!A2:E7;4;FALSE)<>0;
"TRUE";"FALSE");"FALSE"));(IFERROR(IF(VLOOKUP($G4&"*?"&$F4;Sheet3!A2:E7;4;FALSE)<>0
;"TRUE";"FALSE");"FALSE"));(IFERROR(IF(VLOOKUP($G4&$F4;Sheet3!A2:E7;4;FALSE)<>0;
"TRUE";"FALSE");"FALSE")));
AND((IFERROR(IF(VLOOKUP($G4&$F4&"*?";Sheet3!A2:E7;4;FALSE)<>0;"TRUE";"FALSE");
"FALSE"));(IFERROR(IF(VLOOKUP($G4&"*?"&$F4&"*?";Sheet3!A2:E7;4;FALSE)<>0;"TRUE";
"FALSE");"FALSE")));AND((IFERROR(IF(VLOOKUP($G4&"*?"&$F4&"*?";Sheet3!A2:E7;4;FALSE)
<>0;"TRUE";"FALSE");"FALSE"));
(IFERROR(IF(VLOOKUP($G4&"*?"&$F4;Sheet3!A2:E7;4;FALSE)<>0;"TRUE";"FALSE");"FALSE"))
);AND((IFERROR(IF(VLOOKUP($G4&$F4&"*?";Sheet3!A2:E7;4;FALSE)<>0;"TRUE";"FALSE");
"FALSE"));(IFERROR(IF(VLOOKUP($G4&"*?"&$F4;Sheet3!A2:E7;4;FALSE)<>0;"TRUE";"FALSE")
;"FALSE")));AND((IFERROR(IF(VLOOKUP($G4&$F4&"*?";Sheet3!A2:E7;4;FALSE)<>0;"TRUE";
"FALSE");"FALSE"));(IFERROR(IF(VLOOKUP($G4&$F4;Sheet3!A2:E7;4;FALSE)<>0;"TRUE";
"FALSE");"FALSE")));AND((IFERROR(IF(VLOOKUP($G4&"*?"&$F4&"*?";Sheet3!A2:E7;4;FALSE)
<>0;"TRUE";"FALSE");"FALSE"));(IFERROR(IF(VLOOKUP($G4&$F4;Sheet3!A2:E7;4;FALSE)<>0;
"TRUE";"FALSE");"FALSE")));AND((IFERROR(IF(VLOOKUP($G4&"*?"&$F4;Sheet3!A2:E7;4;
FALSE)<>0;"TRUE";"FALSE");"FALSE"));(IFERROR(IF(VLOOKUP($G4&$F4;Sheet3!A2:E7;4;
FALSE)<>0;"TRUE";"FALSE");"FALSE"))));"Multiple";
IF(IFERROR(VLOOKUP($G4&"*?"&$F4&"*?";Sheet3!A2:E7;4;FALSE);"FALSE")<>"FALSE";
VLOOKUP($G4&"*?"&$F4&"*?";Sheet3!A2:E7;4;FALSE);
IF(IFERROR(VLOOKUP($G4&$F4&"*?";Sheet3!A2:E7;4;FALSE);"FALSE")<>"FALSE";
VLOOKUP($G4&$F4&"*?";Sheet3!A2:E7;4;FALSE);
IF(IFERROR(VLOOKUP($G4&"*?"&$F4;Sheet3!A2:E7;4;FALSE);"FALSE")<>"FALSE";
VLOOKUP($G4&"*?"&$F4;Sheet3!A2:E7;4;FALSE);
IF(VLOOKUP($G4&$F4;Sheet3!A2:E7;4;FALSE)<>0;
VLOOKUP($G4&$F4;Sheet3!A2:E7;4;FALSE);)))));"MISSING");"")

 

This is works correctly if the country is not repeated or even if repeats but is in a different position. Like it does for "NO" country. See, below:

 

Dropdown3.PNG

 

Thanks,

Chaitanya Palley

15 Replies

@Chaitanya_Palley 

=IFERROR(INDEX(sheet3!D$2:D$7,SMALL(IF((sheet3!$B$2:$B$7=sheet2!$G$5)*ISNUMBER(SEARCH(sheet2!$F$5,sheet3!$C$2:$C$7)),ROW(sheet3!$D$2:$D$7)-1),ROW(sheet2!1:1))),"")

Does this formula return your expected result? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

sheet2.JPG

sheet3.JPG 

Hi @OliverScheurich , thanks for the response. I used your formula, it still seems to work the same way as vlookup. It is bringing the first instance value when I select "FI". The formula should some way find that "FI" is more than once uniquely and let us to return "Multiple" as there are more than one number values for country "FI"(234 and 567). 

Dropdown4.PNG

 

@Chaitanya_Palley 

I assume that the formula is only entered in cell H4. You can drag the formula down to cell H10 and across the range H4:I10. In my sample sheet you can see that the formula returns all values for number and local language according to the country code and the ID.

@OliverScheurich, Yes I only had the formula in one cell. However, even if I drag it is bringing me all the values into other cells where "FI" exists in the string. But what I am looking for is that in the same cells the formula should understand that there multiple values for that country and throw error which we can customize it to multiple. Is there a way I can use your formula and count the results in the background and do what I am looking for? 

@Chaitanya_Palley 

Can you manually type your expected result in a spreadsheet and attach a picture of this to your reply?

best response confirmed by Chaitanya_Palley (Copper Contributor)
Solution

@Chaitanya_Palley 

=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.

sheet3.JPGsheet4.JPG

 

@OliverScheurich, yes I am using office 365. 

Anyways, the formula seems to return true even if the country column does not have value resulting in returning value regardless of the country selection.

Dropdown6.PNG

@Chaitanya_Palley 

Actually in the expected results you didn't indicate that there is a scenario without a country code:

string match.JPG

 

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:

Dropdown7.PNG

 

@Chaitanya_Palley 

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.

 
 

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

@Chaitanya_Palley 

=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, thank so much!! This too works perfectly for me!! Thanks for all the help.

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. 

 

 

1 best response

Accepted Solutions
best response confirmed by Chaitanya_Palley (Copper Contributor)
Solution

@Chaitanya_Palley 

=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.

sheet3.JPGsheet4.JPG

 

View solution in original post