Formula to find in Col1, output Col2 of same row to Col3 of different row?

nerdesigner
nerdesigner ✭✭✭
edited 12/09/19 in Formulas and Functions

I have content in one row with a Title column and a Description column. 

They are related, but the Description is only populated one time but relevant to any content that has the same Title.

How can I write a formula that says "if (specific)Title, output the content of related Description" to "Output"?

The Title occurs multiple times but the Description doesn't. There are multiple Titles and multiple Descriptions and I'd like to do this for all of them, even if it means a cumbersome custom formula.

Thanks!

 

edit: Includes screenshot, placeholder content.

 

The formula would look at Title, say, is it A? and if it is A, put Description in Output.

So the Output column would always assign the relevant Description.

Screen Shot 2019-04-22 at 11.53.08 AM.png

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • nerdesigner
    nerdesigner ✭✭✭

    Hi Andrée! 

    Thanks for your willingness to help. I updated my post with a screenshot (have to use placeholder content for security, hopefully still makes sense).

    This is user input content so I don't want them to re-define Output each time, all they need to supply is Title and Summary each time, the Description should be the same for each Title.

     

    Maybe I should rewrite my screenshot names to be clearer...

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Let's see if I got this right! wink

    Try something like this.

    =IF(Title@row = "A"; "A is for Apple"; IF(Title@row = "B"; "B is for Bird"))    

    The same version but with the below changes for your and others convenience.    

    =IF(Title@row = "A", "A is for Apple", IF(Title@row = "B", "B is for Bird"))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • nerdesigner
    nerdesigner ✭✭✭

    Here is a better screenshot!

    The Output column is an example of what I want the formula to do!

    I said earlier that users are inputting this content, each row is a form submission.

    So I don't want them to recreate the Description each time, because it stays the same based on the title (Title is a dropdown). All they need to do is tell me the Title SummaryOrder Quantity, and Date.  But for our purposes, Title is most important because whatever they put there, I want Output to match by pulling the relevant Description.

    I use this content for a report, but I would only use Output, not Description in the final output. 

    Thank you!

    Screen Shot 2019-04-22 at 12.09.02 PM.png

  • nerdesigner
    nerdesigner ✭✭✭

    Hi! Yes it does work! But--I hesitate to do it this way is that my actual Description content is WAY longer than the example and possibly subject to change. So it would be ideal for the Output to be the text description in the row/cell, rather than a string I put in the formula.

    Is there a way to output the cell? I tried substituting the "(string)" to be the name of the row and it just comes out blank.

     

    Thank you!!! Really appreciate your help.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    You could have a column for each description and then you would only reference that. Another way could be to set up an INDEX. The best way depends on how many descriptions there might be.

    Try something like.

    Add column A and B and input the description for those in row one of the sheet. All the rows will reference that row and use the correct description.

    =IF(Title@row = "A"; A$1; IF(Title@row = "B"; B$1))    

    The same version but with the below changes for your and others convenience.    

    =IF(Title@row = "A", A$1, IF(Title@row = "B", B$1))

    Would that work?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!