INDEX/MATCH for Multiple Criteria to create single row

Hi all, and thanks in advance for helping, it is greatly appreciated. This was a solved problem, until it was determined that each Release and the stages needed to be on 1 line, rather than 1 for the releases and then the 7 phases below.

The first sheet in my data file which I am linking from. This sheet is owned by someone else, so we did not want our calculations on it.

The second sheet is my initial calculations sheet which was great. There is a unique key on this sheet AND the source file (first screenshot). The JOIN uses the Release Name & Release. Works great. I used Direct Links for The Release Name and Release columns as currently we are only setting up the project with this one. FY24 begins at the end of the month, and I must be ready. All the rest of the formulas on this sheet use INDEX/MATCH. My formula for the Start Date column is:

=INDEX({Start Date}, MATCH([Release Name & Number (Helper)]@row, {Unique ID - Release}, 0))

The Third screenshot represents how I need to transition to a "one line" format. Each of the stages (there are 6) will have a Start & End Date column.

But I am unsure how to incorporate the 2nd criteria into my functional formula. I do know that part of it would be hard coded. I even attempted, and failed. This was what I came up with:

=INDEX({Start Date}, MATCH([Release Name & Number (Helper)]@row, {Unique ID - Release}, 0), MATCH([Release Name & Number (Helper)]@row, "Plan / Develop - Start Date", 0))

Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Tags:

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭

    If you’re looking to evaluate multiple criteria in the index, you’re looking for index collect. You can only pull values from 1 sheet in a single index formula (not sure if that applies here but thought I’d mention it)


    https://community.smartsheet.com/discussion/74301/how-to-correctly-use-index-collect

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • ro.fei
    ro.fei ✭✭✭✭✭

    Hey @Sherry Fox ! I'm not sure I entirely understand which columns you're trying to match up, but I can explain how to set up your formula. If you're looking to do an INDEX/MATCH with multiple criteria, you're going to want to use INDEX/COLLECT instead. Try this:

    = INDEX(COLLECT({Start Date}, [Release Name & Number (Helper))@row, {Unique ID - Release}, [Release Name & Number (Helper)@row, "Plan / Develop - Start Date"), 1)

    Basically the setup is:

    = INDEX(COLLECT(range, criterion1range, criterion1, criterion2range, criterion2...), 1)

    That's the set up you want, but I suspect the column names may be incorrect. For the second criteria, I believe you'll want to reference the Start Date column instead, as you're matching it to "Plan / Develop - Start Date", not the Release Name & Number. If you could provide me with some more context on the column names & what criteria you want for each one, I'd be happy to help you some more if you need it! But this setup of INDEX/COLLECT should help you get started. Feel free to tag me below if you have any questions! 😊

    Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. This will make it easier for others to find a solution or help to answer!

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭

    @ro.fei ,

    Sorry for the delay in responding, I have been out of town. Thanks your explanation of the INDEX/COLLECT formula and how it should be "formatted" really helped me to understand the concept of using multiple criteria within Smartsheet for this function.

    Sherry Fox

    Project Analyst | Core Quality Services (QMS Transformation)

    Medtronic

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • ro.fei
    ro.fei ✭✭✭✭✭

    @Sherry Fox Happy to help! Feel free to tag me if you have any other questions 😊

    Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. This will make it easier for others to find a solution or help to answer!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!