How do I reference a variable column name in a formula?

Options
mwat4482
mwat4482 ✭✭
edited 06/07/23 in Formulas and Functions

I'm trying to reference different columns based on a value in a helper column. Depending on the value in the helper column, the value should be pulled from a different column. I know I can do this with nested IF formulas, but is there a way to have the column name populated in a formula based on lookup formulas?

I wanted the below formula to lookup the column name based on the number in the "Index" column, and then pull the date value from the column header specified.

="[" + INDEX([Index Date Row]:[Index Date Row], MATCH(Index@row, [Index Lookup]:[Index Lookup], 1)) + "]@row"

Example, the first row is Index 3, meaning it should pull the date from "Day 0 Start3 Date" (9/3/23) Instead, it shows as text.


Any suggestions?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!