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

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

Find more posts tagged with

Formulas

Comments

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, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

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å

Happy to help!

Let's see if I got this right!

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

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 , Summary, Order 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

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å

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

Sign in to join the conversation!

Quick Links

Trending discussions

Contact List showing as a text only
It happened suddenly. It was working last week. I'm using Index Collect to pull a contact as cross sheet reference. But today, I did not see that working well. Usually it has circle beside my name, suddenly it vanishes, and it just contains text. I'm using those other columns to be sent notification, and it doesn't work…
Formula to Convert Scientific Notation to Numerical Value
Hi, I am trying to create a sheet from data that is originally held in .csv files. My goal is to make it so that transferring the data to my sheet should be as simple as just copy and paste. My issue is that two of the fields that I need to copy over are reported in scientific notation (1.3E+004) and I am trying to figure…
Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…