** HELP ** Need to add a value based on the value of a drop down list

[sbruk89]

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

[#2]

@sbruk89 

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?

[#3]

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

[#4 · Accepted solution]

@sbruk89 

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