Index/Collect with Multiple Criteria

Darla Brown

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.

Brian_Richardson

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

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)

Darla Brown

This worked! Thank you!