Forum Discussion

sbruk89's avatar
sbruk89
Copper Contributor
Jun 04, 2024
Solved

** 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, loo...
excel
Excel for web
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Jun 04, 2024

    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)
)
HansVogelaar's avatar
HansVogelaar
MVP
Jun 04, 2024

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?

sbruk89's avatar
sbruk89
Copper Contributor
Jun 04, 2024

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

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 04, 2024

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

Resources