Index Collect #INCORRECT ARGUMENT for Checkboxes

kmis_12

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), " "), " ")

MichaelTCA

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), " ")

MichaelTCA

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), " ")

kmis_12

Hello @MichaelTCA,

That worked, thanks for the guidance!