Index/Collect #INCORREECT ARGUMENT SET
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.
Best Answer

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
Answers

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

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.
Help Article Resources
Categories
Check out the Formula Handbook template!