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

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.

Find more posts tagged with

Accepted answers

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), "")

All comments

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.

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), "")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.