INDEX/MATCH with Checkboxes No Results

Options

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! 😖

Answers

  • Lindsay Whitbread
    Options

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

    🤞

    Smartsheet Lead @ InfoSpark

    2023 Asia Pacific Smartsheet Partner of the Year

    Platinum Smartsheet Partner | www.infospark.com.au

  • Miss_Priss
    Options

    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
    Options

    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.
    Courtney S. ✭✭✭✭✭
    Options

    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
    Miss_Priss ✭✭
    edited 05/15/24
    Options

    @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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!