How to use INDEX(COLLECT) for two criteria?
I am trying to use INDEX (COLLECT) to match information from two columns to turn out a result in the third column. This is just a demo (actual variables are different) but here is the general format I'm going for, where I want to get the "occasion" result in a separate datasheet based on the information in "clothing type" and "color."
The formula I am currently using is: =INDEX(COLLECT({Occasion}, Color@row, {Color}, Clothing Type@row, {Clothing Type}, 1)), but it is showing UNPARSEABLE. If anyone has any tips it would be so appreciated!
Best Answer
-
Hi @elm123
It looks like you have one of the closing parentheses in the wrong place. The "1" at the end of the formula is for the INDEX function, not the Collect function.
Try this:
=INDEX(COLLECT({Occasion}, {Color}, Color@row, {Clothing Type}, [Clothing Type]@row), 1)
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Two things:
- In the COLLECT function you need to have the criterion range first and then the criteria, that is, {Color}, Color@row, {Clothing Type}, [Clothing Type]@row
- The parsing error is because you did not enclose the column name in square brackets as the "Clothing Type" column has a space in it. Correct syntax is [Clothing Type]@row
-
@Sameer K thank you for the answer! I now have this:
=INDEX(COLLECT({Occasion}, {Color}, Color@row, {Clothing Type}, [Clothing Type]@row, 1)) and I am getting Incorrect Argument message. How do I fix that?
-
Hi @elm123
It looks like you have one of the closing parentheses in the wrong place. The "1" at the end of the formula is for the INDEX function, not the Collect function.
Try this:
=INDEX(COLLECT({Occasion}, {Color}, Color@row, {Clothing Type}, [Clothing Type]@row), 1)
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. , thank you! It works perfectly now :)
-
I'm using the formula and it works for most of my data but there is some where it says no match found when there is data. I'm using to return the contact name using the title and county as criterion.
-
Hi @Kevin7859
Can you post what formula you're using, as well as a screen capture of the source sheet (but block out sensitive data)?
Would it be possible that the matching value in the source sheet is slightly different than what you're looking for in the formula?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I found the issue was one of the criterion was looking a the incorrect column.
-
I'm trying to follow this but getting the #INCORRECT ARGUMENT SET erro
I have table Codes with "Customer-Job", "JobCode" and "Price"
I have table Accruals where I choose the customer from a dropdown list, enter the job code and hope to get the price.
My formula is =INDEX(COLLECT({Codes Range}, {customer}, [Customer-Job]@row, {job}, JobCode@row), 1)
Looks to be a match to yours but I get that error. Can you tell me what I did wrong?
-
Hi @SeanVt
Your structure is correct, which leads me to believe there may be an error in one of the referenced columns. Do you have formulas in any of these ranges?
- {Codes Range}
- {customer}
- {job}
The other thing to clarify is what columns you're looking at here. Based on your explanation, it sounds like the range you have titled {Codes Range} should be the Price column.
You'll also want to ensure those 3 ranges all have the same amount of cells selected (ensure they're all selecting the entire column) and that they're all coming from the same sheet.
Let us know if any of this helped!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. that was it! I had Codes Range set to the whole table, not just the price column
-
Awesome! Glad you got it sorted 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!