Does INDEX only work with entire column as a range?

itsnotbroken

To be clear, the correct syntax for INDEX is: INDEX(range,  row_index, [ column_index ])

I have several working examples of this function across many sheets, including cross sheet formulas. I also know about using INDEX with MATCH and /or COLLECT to select the appropriate cells based on other criteria.

Up to now, the range has always been an entire column, for example: =INDEX([Column Name]:[Column Name], row_index). This also means that the INDEX formula is in a different column (or on a different sheet) than the actual data we are indexing.

Now I need to pull data from all of the lower rows in the same column, so the range needs to be a range of cells, for example: INDEX([Column Name]$10:[Column Name]$200.

I have the complete formula working fine using the entire column as a range, but it only works on one row at a time. If I copy it to another row, I get a #CIRCULAR REFERENCE error. This is because I'm referring to the entire column in the range and it's in a loop.

If I set an actual range by adding the row numbers to the range as in the example above I get #INCORRECT ARGUMENT SET.

I'm staring to think that INDEX will only work with an entire column as the range, and if this is so, it should be reflected in the documentation.

I would love to get additional input on this.

Thanks,

Mark V

Paul Newcome

It will work with a specified range of cells, but you need to make sure that the rest of the ranges match in size/shape. What is the exact formula that you are getting the error for?

Paul Newcome

It will work with a specified range of cells, but you need to make sure that the rest of the ranges match in size/shape. What is the exact formula that you are getting the error for?

itsnotbroken

Paul,

The requirement to set all the ranges the same makes sense. If we are indexing, they all need to have the same number of rows because we're using the row number to look up the data.

I set all the ranges to the same set of rows & IT'S WORKING!

For others that are looking this up later, a little back story

ICE column is a dollar amount.

[Parent Task] is a helper column that displays the name of the parent task used in the work breakdown structure. Sibling rows will have the same data here so I can index them.

Suffix is another helper column used in the Work Breakdown Structure. It just numbers the siblings incrementally.

There is a task named "ICE" approximately 100 rows down in the sheet. What we are trying to do is pull the data from the ICE column of the children of the row named "ICE". This gets pulled to the top 10-15 rows of the sheet. We want this do be dynamic so we can change the search term & find any row we want. Also, we can copy/paste the formula.

This is the formula in the ICE column that started the discussion:

=INDEX(COLLECT(ICE:ICE, $[Parent Task]:$[Parent Task], $[Parent Task]@row = "ICE"), $Suffix@row

It works perfectly if it is only in one row. If I add it to another row, I get the error. I figured the ICE:ICE reference was causing a loop, so as a test I limited the COLLECT function to a range of rows below the area at the top with the formula in it.

=INDEX(COLLECT(ICE$50:ICE$500, $[Parent Task]:$[Parent Task], $[Parent Task]@row = "ICE"), $Suffix@row

This gives an error of INCORRECT ARGUMENT.

If I set both ranges to the same range per Paul's suggestion, IT WORKS.

=INDEX(COLLECT(ICE$50:ICE$500, $[Parent Task]$50:$[Parent Task]$500, $[Parent Task]@row = "ICE"), $Suffix@row)

Thanks Paul!

Paul Newcome

Happy to help. 👍️