INDEX/MATCH with Checkboxes No Results

Miss_Priss

The formula I'm using isn't giving me an error but for some reason I am not getting any checkbox responses either.

The reference sheet for Agreements has an ID I am trying to match to my current sheet. Then if the checkbox for that row is checked, I want this formula to check the box in my current sheet.

Without the IFERROR statement I get #NOMATCH. When I add the statement the error is gone but I am unable to get any results at all.

The formula is included below:

=IFERROR(INDEX({Agreements Range 1}, MATCH([Partner Account: SPMS ID]@row, {Agreements Range 2} = 1), 1), 0)

I feel like this has to be an easy fix but I've been staring at it for so long and none of the previous community questions seem to align with this problem. Please help! 😖

Lindsay Whitbread

This is a stab in the dark without seeing the sheets themselves, but try this:

=IFERROR(INDEX({Agreements Range 1}, MATCH([Partner Account: SPMS ID]@row, {Agreements Range 2},0)), "")

🤞

Miss_Priss

https://community.smartsheet.com/discussion/comment/429841#Comment_429841

It seems to give the same results; no error but no checkboxes either. I'm not sure where the disconnect is.

This is a view of the Agreements sheet that I am referencing for the index/ match.

This is a view of the sheet I'm working in with the yellow column being where the formula is. From this screenshot you can see that the ID number is matched but the checkbox is not recognized.

In excel when you VLOOKUP I think the result must be to the right of what you're searching for. Is that the same case for INDEX/ MATCH in Smartsheet? Could it be that since the ID in the reference sheet is to the left rather than the right, that it's unable to provide the result?

Miss_Priss

Still having trouble with this one. I'm hoping someone out there has a suggestion based on the discussion chain. I can't seem to get anything to work.

Courtney S.

When I export to Excel the checkboxes with a check actually export as "TRUE" - maybe try using TRUE for the checkbox value instead of 1?

Miss_Priss

@Courtney S. It looks like I'm getting the same result. I'm not really sure what is causing the blank response. I'm at a loss of what to do.

I appreciate you trying to solve it. 🙂

Kelly Moore

Hey @Miss_Priss

Try this

=INDEX(COLLECT({Agreements Range 1}, {Agreements Range 2}, VALUE([Partner Account: SPMS ID]@row)=VALUE(@cell)),1)

or maybe

=INDEX(COLLECT({Agreements Range 1}, {Agreements Range 2}, HAS(@cell,[Partner Account: SPMS ID]@row)),1)

Will either of these work for you?
Kelly