I'm trying to create a formula based on a drop-down list and corresponding columns.

I am trying to build a formula that uses a drop-down list that corresponds to different columns, it needs to capture all of the options. I was using an index match but I can only get one variable to pull and it's not pulling the correct date? A column with Current Phase has multiple drop down options that I need to match to individual columns with dates so it will pull the dates based on the phase into a Phase Completion Deadline column. Columns are not static, so I chose Index rather than VLookup.

=INDEX([Construction Completion Phase Deadline]:[Construction Completion Phase Deadline], MATCH("Construction", [Current Phase]:[Current Phase], 0)

Find more posts tagged with

Accepted answers

All comments

brianschmidt

I'm not 100% sure without seeing the source sheet or knowing more context. If you're able to share screenshots (blurring out any sensitive info), that may help. If you need a formula with multiple conditions, you may need an INDEX(COLLECT) formula. You can set multiple conditions that way. Your base formula with one condition may read something like this:

=INDEX(COLLECT([Construction Completion Phase Deadline]:[Construction Completion Phase Deadline], [Current Phase]:[Current Phase], "Construction"), 1)

However, if you're looking to generate different outcomes in a single cell or column based on variable inputs in other fields, you may need to use nested IF( formulas (i.e. logic states that says "if this is true, do this, if false do this, if false do this..." and so on)

Hope this helps!:)

CFairley

It is helpful but I'm still pulling dates that don't align or I get various error/argument messages. I've included a screenshot as an example.

The current phase has a drop-down of multiple options. Each option has a corresponding column to track against a date. I need the Phase Completion Deadline to pull the date from the column that corresponds to the phase (ie. if Current Phase = Design Development 30%, then I need it to pull the date from the corresponding column if available, once the phase changes, I need it to pull from the next column, etc. for all of the different phases.). I think it's probably the nested IF formula and I'm probably not writing it correctly. Any additional feedback is very much appreciated and I thank you in advance!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.