Using an OR in COLLECT

Andy_DAI · 07/23/19

Hello -

I'm trying to use an OR operator in the criteria of a collect function. This is currently returning an #INCORRECT ARGUMENT SET:

=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR("CHECK", "CREDIT"), 1), ""))

I'm trying to return the a deposit number {WAP Deposit Number} if the Date matches and the type is EITHER "Check" or "Credit"

It's probably something really dumb I'm missing...

Any ideas?

Thanks,

Andy

Andrée Starå · 07/23/19

Hi Andy,

This might help!

https://help.smartsheet.com/function/or

Did it help?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andy_DAI · 07/23/19

Hi Andree -

Thanks - Unfortunately, the syntax is a bit different in the collect function.

It's COLLECT(RANGE, Criterion_range1, criterion1, etc.)

Paul Newcome · 07/23/19

Use @cell references. The OR function is looking for logical statements.

..............OR(@cell = "This", @cell = "That")..............

Andrée Starå · 07/23/19

Happy to help!

I know that, but I didn't have time to write it out before.

Paul Newcome · 07/24/19

I was just looking at this again, and I realized that your parenthesis are also just a little off..

.

Here is your original formula:

=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR("CHECK", "CREDIT"), 1), ""))

.

The parenthesis after "CREDIT" is closing out the OR.

This means that the 1 is actually included in the COLLECT statement (as a range which doesn't work and also happens to be missing a criteria). This pushes "" to your row number in your INDEX function which means that nothing is specified for the second portion of your IFERROR statement.

All of these things on their own will cause the #INCORRECT ARGUMENT SET error.

Add in a closing parenthesis after "CREDIT", and remove one form the end of the formula.

This along with the @cell references in the OR function should clear everything up.

=IFERROR(INDEX(COLLECT({WAP Deposit Number}, {WAP Date}, Date147, {WAP Type}, OR(@cell = "CHECK", @cell = "CREDIT")), 1), "")

Paul Newcome · 07/24/19

EDIT to above (cloudfare blocking issues again):

I also wanted to apologize for missing this earlier. I was so focused on the title of your post trying to use an OR within a COLLECT that I didn't look at the formula as a whole in reference to the error message you were getting.

Let us know if this helps.

Ross Novotny · 12/03/20

@Paul Newcome I have a very similar formula I am trying to write. I am looking to for an index/Collect formula that will pull in a result for either "Location Status'" of "Location Recommended" or "Location Approved". Where did I screw this up?

=IFERROR(INDEX(COLLECT({Master City Name}, {Location Status}, "Location Recommended", OR({Location Status}, "Location Approved")), 1), "")

Paul Newcome · 12/04/20

@Ross Novotny Try this one...

=IFERROR(INDEX(COLLECT({Master City Name}, {Location Status}, OR(@cell = "Location Recommended", @cell = "Location Approved")), 1), "")

ssagers · 03/20/23

@Paul Newcome & @Andrée Starå - Just wanted to thank you for helping so much in these forums! Yet again you gave me an answer. <3

Formulas and Functions

Formulas and Functions

Using an OR in COLLECT

Comments

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Unparseable Error

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed