Can I please get some guidance on making this INDEX/COLLECT formula work?

Andrew Beck 457

I am pretty familiar with using INDEX/COLLECT but I am trying to use it in a context I have never tried before.

This is where I am so far…

=IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, =[Appraisal Item]@row ), 1), "")

I believe I need to use the OR function somehow to continue. I actually have 4 columns @row in my destination sheet that I need to compare to a column in my source sheet to collect data from another column in my source sheet.

I hope this makes sense, any help would be greatly appreciated.

Paul Newcome

You were on the right track with the OR function. Just missing the "@cell" references.

=IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, OR(@cell = [Appraisal Item]@row, @cell = [Internal Item]@row, @cell = [External Item]@row, @cell = [Prevention Item]@row), 1), "")

Andrew Beck 457

This is what I'm trying with the OR function but I keep getting "INVALID OPERATION"

=INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, OR([Appraisal Item]@row = {COQ Source List Range 1}, [Internal Item]@row = {COQ Source List Range 1}, [External Item]@row = {COQ Source List Range 1}, [Prevention Item]@row = {COQ Source List Range 1})), 1)

Andrew Beck 457

Here's my most recent attempt, nothing comes back…

=IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, OR(=[Appraisal Item]@row , =[Internal Item]@row , =[External Item]@row , =[Prevention Item]@row )), 1), "")

Andrew Beck 457

Found the solution.

=IF(NOT(ISBLANK([Appraisal Item]@row )), IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, [Appraisal Item]@row ), 1), ""), IF(NOT(ISBLANK([Internal Item]@row )), IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, [Internal Item]@row ), 1), ""), IF(NOT(ISBLANK([External Item]@row )), IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, [External Item]@row ), 1), ""), IF(NOT(ISBLANK([Prevention Item]@row )), IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, [Prevention Item]@row ), 1), ""), ""))))

Paul Newcome

You were on the right track with the OR function. Just missing the "@cell" references.

=IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, OR(@cell = [Appraisal Item]@row, @cell = [Internal Item]@row, @cell = [External Item]@row, @cell = [Prevention Item]@row), 1), "")

Andrew Beck 457

You also have to add in the NOT(ISBLANK( or it will only run the first column.

Paul Newcome

That should be taken care of with this:

=IFERROR(INDEX(COLLECT({COQ Source List Range 3}, {COQ Source List Range 1}, AND(@cell <> "", OR(@cell = [Appraisal Item]@row , @cell = [Internal Item]@row , @cell = [External Item]@row , @cell = [Prevention Item]@row )), 1), "")