Index Collect #INCORRECT ARGUMENT for Checkboxes
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

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

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
Categories
Check out the Formula Handbook template!