INDEX/MATCH for Multiple Criteria to create single row

Sherry Fox

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))

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

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! 😊

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.

ro.fei

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