INDEX MATCH and COLLECT all at once

David Dolch

I have used INDEX({Array},MATCH(Value in Column),MATCH(Value in Row)) to retrieve a value by matching the column and row

I have used INDEX(COLLECT(Reference Column, Criteria 1, Criteria 2)) to collect

What I am trying to do now is retrieve a value from an array based on the values in two columns and one row

I have a sheet "Time Tracking Sheet" where people enter their time each week, the weeks are in columns and the name and function being tracked are in two separate rows.

I have an other sheet where I am trying to pull in this data "Collection Sheet"

I thought this would work. INDEX(COLLECT(All time tracking data, Primary Col, Collection Name, Function Col, Collection Function, Week, Collection Week),1)

However I keep getting a #INCORRECT ARGUMENT SET error

The real formula I am using is this

=INDEX(COLLECT({Time Tracking Range of Weeks}, {Time Tracking Name}, =PARENT($[Primary Column]@row), {Time Tracking Function}, =[Primary Column]@row, {Time Tracking Header Row}, [Column2]$1), 1)

However, even substituting hard coded values for the criteria, it still does not work

I guess if there is not a formulaic way to accomplish this, I could concatenate the name and function into a helper column and INDEX on that, but I would really like to avoid helper columns.

Thanks for any help :-)

Genevieve P.

Hey @David Dolch

The way I would do this is to add that helper column and + the two values together (and hide them on the sheet).

That way you can use INDEX(MATCH(MATCH to get what you need, since the MATCH needs a single value to reference, as you've found.

=INDEX(Sheet Range, Row, Column)

To get the Row, we need the MATCH to be on its own. You can't use COLLECT within the Match because Collect will adjust the "row numbers" that match sees and you'll always return "1" instead of the sheet row number.

=INDEX({Time Tracking Range of Weeks}, MATCH(PARENT($[Primary Column]@row) + [Primary Column]@row, {Helper Column}, 0), MATCH([Column2]$1, {Time Tracking Header Row}, 0))

Cheers,

Genevieve

Genevieve P.

Hey @David Dolch

The way I would do this is to add that helper column and + the two values together (and hide them on the sheet).

That way you can use INDEX(MATCH(MATCH to get what you need, since the MATCH needs a single value to reference, as you've found.

=INDEX(Sheet Range, Row, Column)

To get the Row, we need the MATCH to be on its own. You can't use COLLECT within the Match because Collect will adjust the "row numbers" that match sees and you'll always return "1" instead of the sheet row number.

=INDEX({Time Tracking Range of Weeks}, MATCH(PARENT($[Primary Column]@row) + [Primary Column]@row, {Helper Column}, 0), MATCH([Column2]$1, {Time Tracking Header Row}, 0))

Cheers,

Genevieve

David Dolch

Thanks Genevieve

That is what I was suspecting. I was hoping there was a way to accomplish this within the formula, as opposed to yet an other helper column, but it works with a helper column so it is not that bad.

-dave