Formula to include verbiage based upon selection

Nicolette Thomas

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

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

Nicolette Thomas

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.

Nicolette Thomas

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

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.