Invalid Value on Index Collect

MikeL2

=IFERROR(INDEX(COLLECT({Checkbox Type Column "ID Finalized" on Smartsheet A}, {Text/Number Type Column "Reference" on Smartsheet A}, [Text/Number Column "ID" in Smartsheet B]@row), 1),"")

The column for {Text/Number Type Column on Smartsheet A} called "Reference" could contain an ID like T1234 or a url https://abc.com

Output is a cell in [Checkbox Type Column "Finalized ID Match" in Smartsheet B] that I would want to either be checked or not checked based on the INDEX COLLECT.

There are more criterion to the formula (why I'm using INDEX COLLECT vs an INDEX MATCH), but I've identified this specific combination in the COLLECT formula as being the reason for the error.

TLDR: Keep getting #INVALID VALUE but the column types seem to line up. If I do a different set of criterion in the above IFERROR INDEX COLLECT frame, the formula works and provides a check in the checkbox insinuating that it is returning a 1 or a 0—just not with the above criterion for some reason.

Paul Newcome

That particular error coming from an INDEX/COLLECT is the equivalent of a #NO MATCH error from an INDEX/MATCH.

This tells me that the formula is unable to find [Text/Number Column "ID" in Smartsheet B]@row in the {Text/Number Type Column "Reference" on Smartsheet A} range.

Paul Newcome

That particular error coming from an INDEX/COLLECT is the equivalent of a #NO MATCH error from an INDEX/MATCH.

This tells me that the formula is unable to find [Text/Number Column "ID" in Smartsheet B]@row in the {Text/Number Type Column "Reference" on Smartsheet A} range.