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!
Best Answers
-
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.
-
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), " ")
Answers
-
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.
-
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.
-
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), " ")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!