** HELP ** Need to add a value based on the value of a drop down list
Hi All,
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
Â
​Hi All,@Hans Vogelaaryou 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   Read More
​
