INDEX MATCH and COLLECT all at once

I have used INDEX({Array},MATCH(Value in Column),MATCH(Value in Row)) to retrieve a value by matching the column and row

I have used INDEX(COLLECT(Reference Column, Criteria 1, Criteria 2)) to collect

What I am trying to do now is retrieve a value from an array based on the values in two columns and one row

I have a sheet "Time Tracking Sheet" where people enter their time each week, the weeks are in columns and the name and function being tracked are in two separate rows.

I have an other sheet where I am trying to pull in this data "Collection Sheet"


I thought this would work. INDEX(COLLECT(All time tracking data, Primary Col, Collection Name, Function Col, Collection Function, Week, Collection Week),1)

However I keep getting a #INCORRECT ARGUMENT SET error

The real formula I am using is this

=INDEX(COLLECT({Time Tracking Range of Weeks}, {Time Tracking Name}, =PARENT($[Primary Column]@row), {Time Tracking Function}, =[Primary Column]@row, {Time Tracking Header Row}, [Column2]$1), 1)

However, even substituting hard coded values for the criteria, it still does not work

I guess if there is not a formulaic way to accomplish this, I could concatenate the name and function into a helper column and INDEX on that, but I would really like to avoid helper columns.

Thanks for any help :-)

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hey @David Dolch

    The way I would do this is to add that helper column and + the two values together (and hide them on the sheet).

    That way you can use INDEX(MATCH(MATCH to get what you need, since the MATCH needs a single value to reference, as you've found.

    =INDEX(Sheet Range, Row, Column)

    To get the Row, we need the MATCH to be on its own. You can't use COLLECT within the Match because Collect will adjust the "row numbers" that match sees and you'll always return "1" instead of the sheet row number.

    =INDEX({Time Tracking Range of Weeks}, MATCH(PARENT($[Primary Column]@row) + [Primary Column]@row, {Helper Column}, 0), MATCH([Column2]$1, {Time Tracking Header Row}, 0))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hey @David Dolch

    The way I would do this is to add that helper column and + the two values together (and hide them on the sheet).

    That way you can use INDEX(MATCH(MATCH to get what you need, since the MATCH needs a single value to reference, as you've found.

    =INDEX(Sheet Range, Row, Column)

    To get the Row, we need the MATCH to be on its own. You can't use COLLECT within the Match because Collect will adjust the "row numbers" that match sees and you'll always return "1" instead of the sheet row number.

    =INDEX({Time Tracking Range of Weeks}, MATCH(PARENT($[Primary Column]@row) + [Primary Column]@row, {Helper Column}, 0), MATCH([Column2]$1, {Time Tracking Header Row}, 0))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • David Dolch
    David Dolch ✭✭✭✭✭

    Thanks Genevieve

    That is what I was suspecting. I was hoping there was a way to accomplish this within the formula, as opposed to yet an other helper column, but it works with a helper column so it is not that bad.


    -dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!