Index/Collect with Multiple Criteria

Darla Brown
Darla Brown Overachievers
edited 03/25/24 in Formulas and Functions

I'm having issues with this formula. It's returning #Invalid Value. Once I can get this to work, I have 4 more criteria to add. The "Sub Dept" columns are single-select drop-down. The Assigned column and the column the formula sits in are Contact List.

=INDEX(COLLECT({Assignments by Discipline Test Assigned}, {Assignments by Discipline Test}, @cell = [Accounting Sub Dept]@row, {Assignments by Discipline Test}, @cell = [Billing/Credit Sub Dept]@row), 1)

I've tried without @cell= and with it, and I've also tried eliminating the 1 at the end. Nothing is working.

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓

    If it's the same column for all your criteria then you should be able, instead, to use a single OR criteria.

    Try this:

    =INDEX(COLLECT({Assignments by Discipline Test Assigned},{Assignments by Discipline Test}, OR(@cell=[Accounting Sub Dept]@row , @cell=[Billing/Credit Sub Dept]@row)),1)

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓

    If it's the same column for all your criteria then you should be able, instead, to use a single OR criteria.

    Try this:

    =INDEX(COLLECT({Assignments by Discipline Test Assigned},{Assignments by Discipline Test}, OR(@cell=[Accounting Sub Dept]@row , @cell=[Billing/Credit Sub Dept]@row)),1)

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Darla Brown
    Darla Brown Overachievers

    This worked! Thank you!

    Darla Brown

    What you meditate on, you empower!

    Overachiever - Core Product Certified - Mobilizer - EAP

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!