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?