Index Collect and Min Collect with Contains
Hi there!
I'm working on a formula that I need to add a criteria that if the Tracking Status column contains "In Review", it will return the cell value from the index.
Here is what I have that is working, but unfortunately, will pull in anything with the matching subject even if it's Tracking Status "Expired":
=IFERROR(INDEX(COLLECT({Value from Column I'd Like Returned When Criteria Met}, {Helper Sheet Subject Column}, [Main Sheet Subject Column]@row, {Helper Sheet Row ID Number}, MIN(COLLECT({Helper Sheet Row ID Number}, {Helper Sheet Subject Column}, [Main Sheet Subject Column]@row))), 1), "")
The gist is:
- I have a helper sheet that contains two copies of the same row from the main sheet
- each row is auto-numbered in sequence from when it was copied over to the helper sheet
- the formula in main sheet column A is asking to return value from a column in helper sheet based on matching the Subject column from main and helper sheet. The formula is also asking for it to return the MIN row number of the two copies of the same row in the helper sheet.
- there is also another formula in main sheet column B that is asking to return value from a column in helper sheet based on matching the Subject column from main and helper sheet. This formula is asking for it to return the MAX row number of the two copies of the same row in the helper sheet.
I'm trying to add an additional criteria CONTAINS to this formula that only returns value wanted above if the helper sheet Tracking Status column contains "In Review".
Each time I try to add it, I get #INCORRECT ARGUEMENT SET.
HELP PLEASE!
Best Answer
-
Glad it helped!
I think from what you've said above you have another criteria range and criteria for the MIN(COLLECT part of the formula.
So I think your formula would look like
=(IF(OR(CONTAINS("04", [Tracking Status]@row), CONTAINS("01", [Tracking Status]@row), CONTAINS("02", [Tracking Status]@row)), IFERROR(INDEX(COLLECT({Smartsheet Information Repository [MISOS] Range 1}, {Smartsheet Information Repository [MISOS] Range 4}, Subject@row, {Smartsheet Information Repository [MISOS] Range 5}, MIN(COLLECT({Smartsheet Information Repository [MISOS] Range 5}, {Smartsheet Information Repository [MISOS] Range 4}, Subject@row,{New Range described above}, [Company]@row))), 1), ""), ""))
The above should make sure that you are matching up the information you need but also adding in the condition that it is also from the same company.
Hope it helps
Answers
-
Would this help?
=(IF(CONTAINS("In Review", [Tracking Status]@row),IFERROR(INDEX(COLLECT({Value from Column I'd Like Returned When Criteria Met}, {Helper Sheet Subject Column}, [Main Sheet Subject Column]@row, {Helper Sheet Row ID Number}, MIN(COLLECT({Helper Sheet Row ID Number}, {Helper Sheet Subject Column}, [Main Sheet Subject Column]@row))), 1), ""),"")
-
This was perfect!
Figured out that I needed to add an OR between IF and CONTAINS because there could be 3 options that should return the result, took me a sec, but with yours and this https://community.smartsheet.com/discussion/97349/if-or-contains I got to
=(IF(OR(CONTAINS("04", [Tracking Status]@row), CONTAINS("01", [Tracking Status]@row), CONTAINS("02", [Tracking Status]@row)), IFERROR(INDEX(COLLECT({Smartsheet Information Repository [MISOS] Range 1}, {Smartsheet Information Repository [MISOS] Range 4}, Subject@row, {Smartsheet Information Repository [MISOS] Range 5}, MIN(COLLECT({Smartsheet Information Repository [MISOS] Range 5}, {Smartsheet Information Repository [MISOS] Range 4}, Subject@row))), 1), ""), ""))
The craziest formula I've used so far!
Appreciate the quick and exact help!
-
Actually, is there a way to add where it would match the company column between the main and helper sheet as an additional layer to ensure I'm capturing the correct return?
On occasion the same Subject Name might change hands between companies, and it's for the MIN, it's return the first Subject Name match, which might not be the right company, if that makes sense.
-
Glad it helped!
I think from what you've said above you have another criteria range and criteria for the MIN(COLLECT part of the formula.
So I think your formula would look like
=(IF(OR(CONTAINS("04", [Tracking Status]@row), CONTAINS("01", [Tracking Status]@row), CONTAINS("02", [Tracking Status]@row)), IFERROR(INDEX(COLLECT({Smartsheet Information Repository [MISOS] Range 1}, {Smartsheet Information Repository [MISOS] Range 4}, Subject@row, {Smartsheet Information Repository [MISOS] Range 5}, MIN(COLLECT({Smartsheet Information Repository [MISOS] Range 5}, {Smartsheet Information Repository [MISOS] Range 4}, Subject@row,{New Range described above}, [Company]@row))), 1), ""), ""))
The above should make sure that you are matching up the information you need but also adding in the condition that it is also from the same company.
Hope it helps
-
@Gillian C Works perfectly!
Thank you so much for taking the time to assist me :)
-
@ Jonna Critchley super 😀 happy to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!