I need some guidance please. I have LOOKUP formulas working in other sheets, but I need this operation to work sort of backwards.

In NWM1.png below, I show the columns I'm working with, this sheet is populated via a Smartsheet form, per our usual method.

"Initiative" needs to be populated relative to a value that will be form entered into one of the 5 columns to the right. Each of those columns is a drop-down style, selected in the form. Each of those 5 columns has 1 - 6 options.

NWM2.png shows column CD1 with all of the options available in the 5 columns to the right of "Initiative" column. Column CD2 shows the 5 options that should populate "Initiative" based on the value selected in one of the 5 columns.

I.e., If "Planning" is selected under ProjPrep-Deliverable, then "initiative" should be populated with "Project Preparation".

"AgenRec-Deliverables" can have 1 of 4 options selected, but should populate "Initiative" with "Agenda Recommendations" no matter which of the 4 selections is selected in the form.

Is this possible?




Can you provide screenshot with dummy data of how you want this to look? I think I have an idea of what you are trying to do, but I am not 100% certain I am following.