Jul 11 2022 08:27 AM
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.
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.
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:
Thanks,
Chaitanya Palley
Jul 11 2022 09:00 AM
=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.
Jul 11 2022 09:42 AM
Hi @Quadruple_Pawn , 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).
Jul 11 2022 10:05 AM
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.
Jul 11 2022 10:14 AM
@Quadruple_Pawn, 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?
Jul 11 2022 10:37 AM
Can you manually type your expected result in a spreadsheet and attach a picture of this to your reply?
Jul 11 2022 11:32 AM
@Quadruple_Pawn, here you go:
Jul 11 2022 12:22 PM
Solution=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.
Jul 12 2022 04:19 AM
@Quadruple_Pawn, 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.
Jul 12 2022 05:12 AM
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.
Jul 12 2022 10:49 AM
@Quadruple_Pawn, 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:
Jul 12 2022 11:43 AM
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.
Jul 13 2022 03:00 AM
@Quadruple_Pawn, 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))
Jul 13 2022 04:28 AM
=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.
Jul 13 2022 06:11 AM
@Quadruple_Pawn, thank so much!! This too works perfectly for me!! Thanks for all the help.
Aug 19 2022 05:58 AM - edited Aug 22 2022 03:56 AM
Hello @Quadruple_Pawn , 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.