I have used the index and collect function successfully when using just a range in the formulation. But is seems like if I try to reference the whole column either as the range or the criteria, then I get the #unparseable value.
Reason being that when new rows are added, I want this as a Column formula since the new rows will increase the range, thus would have to be manual copied down.
I have three columns: CAT is the category value that we want to find; CM is the Current Month (as a number) as one criteria and the other criteria column is MATERIAL.
So find the material based on a month and return the value from the Category field. Here is how it works when I just highlight the range of the current rows:
=INDEX(COLLECT(Cat1:Cat8, Material1:Material8, Material@row, CM$:CM$, PM@row), 1)
So it works on my 8 row test as it takes the material # from the current row, and looks back to the previous 7 rows and sees if it finds it based on the month we tell it from another colums. So bascially we are capturing the previous value of the current material by looking back up the list.
However, if I convert any of those ranges to reference the full column, the formula breaks
