Forum Discussion
** HELP ** Need to add a value based on the value of a drop down list
Hi All,
HansVogelaar
you wrote this formula for me, which works a treat 🙂 so thank you!
=LET(
lookupValue, Concatinator!B2,
lookupRange, Catalogue!G2:N2,
colIndex, MATCH(lookupValue, lookupRange, 0),
matchedColumn, INDEX(Catalogue!G3:N1000, , colIndex),
filteredData, FILTER(
Catalogue!D3:D1000 & " " & Catalogue!F3:F1000 & " " & Catalogue!E3:E1000,
(Catalogue!C3:C1000 = Sheet3!A2) * ((matchedColumn = "Y") + (matchedColumn = "YF") + (matchedColumn = "YA")),
""
),
TEXTJOIN("; ", TRUE, filteredData)
)
However, I have one further ask. I need another value to be added when its being concatenated. So, if the value in Cell B2 on the Concatinator tab is equal to lets say "XXX DACH" then i want it to search in Column J on the Catalogue Tab and if a "YF" is present then i want it to add "DE" after Column E and the same if "XXX Italy" has been selected. However, if any other value is selected then it should just work as it currently does.
Concatinator tab
Catalogue Tab
Does this do what you want?
=LET( lookupValue, Concatinator!$B$2, lookupRange, Catalogue!$G$2:$N$2, colIndex, MATCH(lookupValue, lookupRange, 0), matchedColumn, INDEX(Catalogue!$G$3:$N$890, , colIndex), filteredData, FILTER( Catalogue!$D$3:$D$890 & " " & Catalogue!$F$3:$F$890 & " " & Catalogue!$E$3:$E$890, (Catalogue!$C$3:$C$890 = Sheet3!A2) * ((matchedColumn = "Y") + (matchedColumn = "YF") + (matchedColumn = "YA")), "" ), extra, IFS(ISNUMBER(SEARCH("DACH", Concatinator!$B$2))*ISNUMBER(XMATCH("YF", matchedColumn)), "DE", ISNUMBER(SEARCH("IT", Concatinator!$B$2))*ISNUMBER(XMATCH("YF", matchedColumn)), "IT", TRUE, ""), TEXTJOIN(" ", TRUE, TEXTJOIN("; ", TRUE, filteredData), extra) )
3 Replies
I have a hard time getting my head around that. Could you attach a sample workbook with a couple of examples of the expected output?
HansVogelaar
So, currently it pulls through the values from Columns s E & F, however if XXX DACH is chosen, as in this example and there is a "YF" present in Column J on "Catalogue Tab", I want to add "DE" after the value from Column "E" so in this example it would be something like XXX Stream standalone in Austria Q1 '25 (Value from Column F) †♦ (Value from Column E) +"DE"
Concatinator Tab:
Catalogue Tab:
So from this tab we should get the following results
XXX Stream standalone in Austria Q1 '25 †♦ "+DE"XXX Stream standalone in Germany Q3 †♦ "+DE"
XXX Stream standalone in Italy Q2 †♦ (I wouldnt expect "+DE")on that same note if i selected XXX Italy on the concatinator tab in cell b2 I should only expect to see
Sky Stream standalone in Austria Q1 '25 †♦ (i wouldnt expect to see "+DE")
Sky Stream standalone in Germany Q3 †♦ (i wouldnt expect to see "+DE")
XXX Stream standalone in Italy Q2 †♦ (+IT)
I have attached a copy of the documentDoes this do what you want?
=LET( lookupValue, Concatinator!$B$2, lookupRange, Catalogue!$G$2:$N$2, colIndex, MATCH(lookupValue, lookupRange, 0), matchedColumn, INDEX(Catalogue!$G$3:$N$890, , colIndex), filteredData, FILTER( Catalogue!$D$3:$D$890 & " " & Catalogue!$F$3:$F$890 & " " & Catalogue!$E$3:$E$890, (Catalogue!$C$3:$C$890 = Sheet3!A2) * ((matchedColumn = "Y") + (matchedColumn = "YF") + (matchedColumn = "YA")), "" ), extra, IFS(ISNUMBER(SEARCH("DACH", Concatinator!$B$2))*ISNUMBER(XMATCH("YF", matchedColumn)), "DE", ISNUMBER(SEARCH("IT", Concatinator!$B$2))*ISNUMBER(XMATCH("YF", matchedColumn)), "IT", TRUE, ""), TEXTJOIN(" ", TRUE, TEXTJOIN("; ", TRUE, filteredData), extra) )
