Formula to include verbiage based upon selection

Hi all,

I am working on a sheet in which I need a formula that when a status is selected in one column that additional verbiage would pre-populate another column.

The picture below in the status column, when I select the word "cancelled", I would like the comments column to show the word "event is not going to happen"

I'll need seven of these done so not sure of the most efficient way to get this done. Any help is appreciated.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are two ways...

    Create a nested IF that covers everything:

    =IF(EventStatus@row = "Cancelled", "Not Happening", I(EventStatus@row = "Postponed - TBD", "Verbiage for that one", IF(.......................................................)))))))

    Or you can create a two column table that has each status in one column and the verbiage in the other and use a VLOOKUP or INDEX/MATCH. This allows for greater flexibility because if you want to change or add or remove a status, you can do so on the table instead of having to edit the formula itself and then update every instance of the formula on the sheet.

    =INDEX(VerbiageTable:VerbiageTable, MATCH(EventStatus@row, StatusTable:StatusTable, 0))

  • Thanks Paul. Index match seems to be the way to go just having trouble with writing it. I'm missing something which is not unusal

    Let me try the formula that you have provided. I may have more questions.

  • I'm doing something wrong. It is showing no match. I built the legend and the descriptors right next to it. The comments section is where I want the descriptors to show up based upon the selection but it is not showing properly. Showing no match.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Switch your ranges. The first range in the INDEX function is the range you want to pull data from. The MATCH function is what you use to compare the status.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!