Index Collect and Min Collect with Contains

Jonna Critchley

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!

Gillian C

@Jonna Critchley

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

@Jonna Critchley

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), ""),"")

Jonna Critchley

@Gillian C

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!

Jonna Critchley

@Gillian C

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.

Gillian C

@Jonna Critchley

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

Jonna Critchley

@Gillian C Works perfectly!

Thank you so much for taking the time to assist me :)

Gillian C

@ Jonna Critchley super 😀 happy to help.