How to correctly use INDEX(COLLECT())

Sophie Knudson

I am trying to create a cross-sheet formula that pulls the data from another sheet if it meets 3 criteria.

In the source sheet, I need the following criteria met:

1. "Month/Year" column to be the same as the "Month/Year of Report" column in my target sheet.

2. "Activity code" column to be "2105532"

3. "Name" column to be "Sophie Knudson"

I then want the cell within the "Hours" column in the source sheet that meets all of that criteria to be automatically populated into my target sheet.

Could someone please help me with creating this formula? This is what I've tried and I keep getting #UNPARSEABLE messages.

=INDEX(COLLECT({Source Sheet Range 5}, {Source Sheet Range 2}, [Month/Year of report]@row, [{Source Sheet Range 3}, "Sophie Knudson", {Source Sheet Range 4}, "2105532"]))

Your support is greatly appreciated.

Paul Newcome

First you will need to remove the square brackets from before Range 3 and after "2105532". Then between the closing parenthesis at the end you will need to add ", 1".

=INDEX(COLLECT({Source Sheet Range 5}, {Source Sheet Range 2}, [Month/Year of report]@row, {Source Sheet Range 3}, "Sophie Knudson", {Source Sheet Range 4}, "2105532"), 1)

Paul Newcome

First you will need to remove the square brackets from before Range 3 and after "2105532". Then between the closing parenthesis at the end you will need to add ", 1".

=INDEX(COLLECT({Source Sheet Range 5}, {Source Sheet Range 2}, [Month/Year of report]@row, {Source Sheet Range 3}, "Sophie Knudson", {Source Sheet Range 4}, "2105532"), 1)

Sophie Knudson

Thanks Paul! I tried this adjustment and received an #INCORRECT ARGUMENT error code. Any suggestions for fixing this?

Paul Newcome

Hmm.... Are you able to copy/paste the formula directly from your sheet to here?

Sophie Knudson

Yep! Here's what I have:

=INDEX(COLLECT({GHSC-QA Monthly Labor Hours Description Range 5}, {GHSC-QA Monthly Labor Hours Description Range 2}, [Month/Year of report]@row, {GHSC-QA Monthly Labor Hours Description Range 3}, "Sophie Knudson", {GHSC-QA Monthly Labor Hours Description Range 4}, "2105532"), 1)

Paul Newcome

It looks like your syntax is correct which leads me to believe one or more of your ranges do not match. Double check that all of your ranges are the same size/shape meaning they are all referencing a single column or they are all referencing the same size/shape grid or whatever your particular case may be.

Sophie Knudson

Oh the first range is multiple columns, all of the columns that will be used as criterion ranges. Is that correct?

Paul Newcome

With the INDEX function, you would only select the column that you want to pull from.

Sophie Knudson

Ah that solved it! Thank you so much!

Paul Newcome

Happy to help. 👍️

Ryan Jezierski

I'm having a similar issue where I'm trying to pull a production start time from another sheet based off of a batch id. I created a helper column in the source sheet that populates with the batch id if the start time is not blank as there are multiple entries from the same batch id. I'm currently getting incorrect argument set with this formula:

=INDEX(COLLECT({2021 Packaging Production Range 2}, {2021 Packaging Production Range 7}, Batch@row), 1)

Where "2021 Packaging Production Range 2" is the column with start times, "2021 Packaging Production Range 7" is the helper column with batch ID, & "Batch@row" is the batch ID I am trying to match it to.

MPath

@Paul Newcome Just letting you know that 3years later this is still helpfull. I was strugling with this. Don't still understand whats up with the , 1 just before the last parenthesis, and was using without index fucntion, don't quite get that either, i like such a noob! ;)

Thank you for spending time here!

Paul Newcome

@MPath The COLLECT function basically creates an array of all cells from the first range that meet the various range/criteria sets.

The 1 before the last parenthesis is part of the INDEX function. We use the INDEX function to evaluate the list generated by the COLLECT function and tell it to pull the first entry on that list.

MPath

Thanks, that make is very clear to me.

Amber NG

I'm having a similar problem with my Index Collect function. I'm receiving an "#INVALID VALUE" error.

=INDEX(COLLECT({Mgmt Status}, {Project Code}, [Project Code]@row, {Status Date}, "3 / 20 / 24"), 1)

I am trying to pull in historical status RYG balls for metrics.

Any help is super appreciated!

Thank you so much,

Amber

Genevieve P.

Hi @Amber NG

Is your {Status Date} column a date type of column? If so, you'll need the criteria listed afterwards to be a Date, versus text in quotes. Try using the DATE Function:

=INDEX(COLLECT({Mgmt Status}, {Project Code}, [Project Code]@row, {Status Date}, DATE(2024, 03, 20)), 1)

Cheers,

Genevieve