Index Collect Has with a multi select reference column

I am working on a formula that will reference a column in Sheet A that may have multiple choices selected, and compare it to a column in Sheet B that will match one of the selections in Sheet A and return the word 'Completed'

I've tried these 2 formulas and both return #INCORRECT ARGUMENT

=INDEX(COLLECT({Equipment inspected}, HAS(Equipment@row ), "Completed"))

=INDEX(COLLECT({Equipment inspected}, HAS(@cell , $Equipment$1), "Completed"), 1)

Left side is Sheet A the reference sheet column. Right side is Sheet B that I want a return value of 'Completed'.

Find more posts tagged with

Accepted answers

Alicia L

I got it to work with this formula

=IF(COUNTIFS({Equipment inspected}, HAS(@cell , Equipment@row )) > 0, "Completed")

Thank you

All comments

Paul Newcome

You are missing a range. The COLLECT's first range is the "range to collect" based on the range/criteria sets that follow. It looks like maybe you forgot to specify the "range to collect" as well as specifying the range for the "Completed" criteria.

=INDEX(COLLECT({Completed Column}, {Equipment inspected}, HAS(@cell, $Equipment$1), {Completed Column}, "Completed"), 1)

You are also going to want to include an IFERROR function so that you don't get any errors if there are no rows for that particular piece of equipment that have been marked as "Completed".

=IFERROR(INDEX(COLLECT({Completed Column}, {Equipment inspected}, HAS(@cell, $Equipment$1), {Completed Column}, "Completed"), 1), "")

Having said that, I personally prefer to use an IF/COUNTIFS combo for things like this. I use a COUNTIFS to count how many rows match my criteria, then I use an IF to say that IF it is greater than zero (meaning at least one row is marked "Completed"), then output "Completed". Not that that means my method is better. It is just my personal preference and another option for these cases (plus less characters to type).

=IF(COUNTIFS({Equipment inspected}, HAS(@cell, $Equipment$1)) > 0, "Completed")

Alicia L

My reference sheet does not have a Completed column, so the suggested formula does not work. I adjusted it to this formula, but it returns a #circular reference.

=IFERROR(INDEX(COLLECT([Audit on equipment completed]@row , {Equipment inspected}, HAS(@cell , $Equipment$1), [Audit on equipment completed]@row , "Completed"), 1), "")

Paul Newcome

If it does not have a completed column, try an IF/COUNTIFS combo similar to what I suggested in my last comment instead (but update the COUNTIFS to only be the one range/criteria set that you need).

Alicia L

I got it to work with this formula

=IF(COUNTIFS({Equipment inspected}, HAS(@cell , Equipment@row )) > 0, "Completed")

Thank you

Paul Newcome

Happy to help. 👍️

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.