Multiple Match Criteria

Alexandra Prescott

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!

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

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