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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @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
    Megan Graff ✭✭
    Answer ✓

    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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @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.

  • 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
    Megan Graff ✭✭
    Answer ✓

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!