Index Collect #INCORRECT ARGUMENT for Checkboxes

kmis_12
kmis_12
edited 06/05/24 in Formulas and Functions

Hello,

I'm trying to return a date in sheet A based on three dependencies from sheet B - order type, SKU, and whether or not a checkbox is ticked.

The first two functions work using Index Collect, though results are incorrect based on not factoring the checkbox.

=IFERROR(INDEX(COLLECT({Date}, {Order Type}, "Delivery", {SKU}, [SKU]@row), 1), " ")

When I try to expand the Collect field to include unchecked boxes, the argument breaks:
=IFERROR(INDEX(COLLECT({Date}, {Order Type}, "Delivery", {SKU}, [SKU]@row, {Redelivery}, "0"), 1), " ")

Is there a different format I should use for checkboxes? I also tried adding it as an IF function, but this didn't work either:

=IF({Redelivery} = 0, IFERROR(INDEX(COLLECT({Date}, {Order Type}, "Delivery", {SKU}, [SKU]@row), 1), " "), " ")

Best Answer

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓

    Hello @kmis_12

    The checkbox is called a Boolean variable type. The values are either True or False and 1 or 0.

    Your function is written as "0", which means you are looking for the numeric value of 0. This would work if the value was in a text/number column.

    Remove the quotes around the 0 or change the criteria to False, and it should work as you hoped.

    =IFERROR(INDEX(COLLECT({Date}, {Order Type}, "Delivery", {SKU}, [SKU]@row, {Redelivery}, FALSE or 0 with no quotes), 1), " ")

Answers

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓

    Hello @kmis_12

    The checkbox is called a Boolean variable type. The values are either True or False and 1 or 0.

    Your function is written as "0", which means you are looking for the numeric value of 0. This would work if the value was in a text/number column.

    Remove the quotes around the 0 or change the criteria to False, and it should work as you hoped.

    =IFERROR(INDEX(COLLECT({Date}, {Order Type}, "Delivery", {SKU}, [SKU]@row, {Redelivery}, FALSE or 0 with no quotes), 1), " ")

  • Hello @MichaelTCA,

    That worked, thanks for the guidance!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!