Multiple Match Criteria

I'm working on a formula to return an account number if the criteria matches for Client AND Service Description. For example, if a user selects Medical Education from a Service Description dropdown list when creating a work order for Client X, the corresponding Medical Education account number for Client X should be returned.

I tried the Index Collect formula below but got an Incorrect Argument error.

=INDEX(COLLECT({IO Number}, {Client}, [Client Name]@row, {Service Description}, [Service Description]@row, 1))

Am I on the right track or is there another combination that I could use?

Thank you!

Find more posts tagged with

Accepted answers

Mike TV

@Alexandra Prescott

You are very close.

=INDEX(COLLECT({IO Number}, {Client}, [Client Name]@row, {Service Description}, [Service Description]@row), 1)

You just had the parenthesis at the end out of place a tiny bit. You needed to wrap up your COLLECT criteria and ranges with the parenthesis before indicating the row index number.

Megan Graff

Hi Alexandra,

To get rid of the #INVALID VALUE, you can wrap your formula with an =IFERROR. To end with an empty cell, your formula will look as follows:

=IFERROR(INDEX(COLLECT({IO Number}, {Client}, [Client Name]@row, {Service Description}, [Service Description]@row), 1), " ")

All comments

Mike TV

@Alexandra Prescott

You are very close.

=INDEX(COLLECT({IO Number}, {Client}, [Client Name]@row, {Service Description}, [Service Description]@row), 1)

You just had the parenthesis at the end out of place a tiny bit. You needed to wrap up your COLLECT criteria and ranges with the parenthesis before indicating the row index number.

Alexandra Prescott

Thank you! This worked great. What do I add to leave the cell blank if the criteria are not met? Right now, the cell reads #INVALID VALUE if the row doesn't have a Service Description. I'd like for it to be blank.

Megan Graff

Hi Alexandra,

To get rid of the #INVALID VALUE, you can wrap your formula with an =IFERROR. To end with an empty cell, your formula will look as follows:

=IFERROR(INDEX(COLLECT({IO Number}, {Client}, [Client Name]@row, {Service Description}, [Service Description]@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.