INDEX/COLLECT #INCORRECT ARGUMENT error

saltyblueocean

I have been hitting a wall with an INDEX/COLLECT formula that operates within the confines of a single sheet (no cross sheet formulas). For background on the sheet we use it to track results from various tests performed on a product at various stages. What I am hoping to have the formula do is identify any time a test has been positive on previous tests and collect that into a single column for easy review in a report.

So for example, this is what I would want to see in the previous positives column based on the results of earlier tests:

I have been getting an #INCORRECT ARGUMENT error with every step of the formula, even when I break it down to the most simple version of it without a ton of varying criterion.

The current formula with the least number of criterion reads like this: =INDEX(COLLECT([Test #]:[Test #], [Batch #]:[Batch #], [Batch Number]@row))

This is obviously a much more simplified version of the sheet, so I can add in screenshots of the actual sheet. But hoping the piece I am missing is glaringly obvious to others!

Paul Newcome

First you are going to want to insert a column that combines the Batch and Test numbers into a single string.

=[Batch #]@row + "-" + [Test #]@row

Finally the formula you are going to wan to use is going to look like this...

=JOIN(COLLECT([Helper Column]:[Helper Column], [Batch #]:[Batch #], @cell = [Batch #]@row, [Test #]:[Test #], @cell< [Test #]@row, Result:Result, @cell = "positive"), ", ")