Index/Collect #INCORREECT ARGUMENT SET

EMH 2021

We have a formula error I just can't seem to correct.

I want to return an abbreviation in column "CaseType" based on the value in column "Petition Type"

I am using the following formula:

=INDEX(COLLECT({Petition/Case Type Matrix Range 5}, [Petition Type]@row, {Petition/Case Type Matrix Range 2}, 1,))

References:

Petition/Case Type Matrix Range 2 – Petition Type Column

Petition/Case Type Matrix Range 5 – Case Type

Our Smartsheets rep helped us build it a year ago, someone edited the references sheet and now it is out of order. I am sure it is a simple fix, but for the life of me I cannot find it.

Main Sheet.jpg

Reference Sheet.jpg

Kelly Moore

Hey @EMH 2021

I made a few corrections to the syntax of your formula to agree with the required syntax of the COLLECT function: =COLLECT(range to collect, range1, criteria1, range2, criteria2, etc).

=INDEX(COLLECT({Petition/Case Type Matrix Range 5}, {Petition/Case Type Matrix Range 2}, [Petition Type]@row),1)

You're right that an INDEX/COLLECT will work. Since you have only one criteria that you're matching against ([Petition Type]@row), you could use an INDEX/MATCH instead. Your formula above almost matched this syntax

=INDEX({Petition/Case Type Matrix Range 5}, MATCH([Petition Type]@row,{Petition/Case Type Matrix Range 2},0))

As you continue to build formulas in the future, consider the good practice of renaming the generically assigned cross sheet range numbers with the actual column names used in the range. This will help you and others better understand the formulas.

cheers

Kelly

Here are the functions I used

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

https://help.smartsheet.com/function/collect

Kelly Moore

Hey @EMH 2021

I made a few corrections to the syntax of your formula to agree with the required syntax of the COLLECT function: =COLLECT(range to collect, range1, criteria1, range2, criteria2, etc).

=INDEX(COLLECT({Petition/Case Type Matrix Range 5}, {Petition/Case Type Matrix Range 2}, [Petition Type]@row),1)

You're right that an INDEX/COLLECT will work. Since you have only one criteria that you're matching against ([Petition Type]@row), you could use an INDEX/MATCH instead. Your formula above almost matched this syntax

=INDEX({Petition/Case Type Matrix Range 5}, MATCH([Petition Type]@row,{Petition/Case Type Matrix Range 2},0))

As you continue to build formulas in the future, consider the good practice of renaming the generically assigned cross sheet range numbers with the actual column names used in the range. This will help you and others better understand the formulas.

cheers

Kelly

Here are the functions I used

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

https://help.smartsheet.com/function/collect

EMH 2021

Perfect! Your point on the naming is well taken. I learned the hard way that one should be careful on thoughtful when naming the references or you spend half your time trying to figure them out.