Check box if matching value in other sheet

Hi All,

I am attempting to write a formula that would check a box if a value is present in another sheet, and I can't figure out exactly how to write it. I am thinking it would be a combination of IF, INDEX, and MATCH.

I have two sheets, both of which have a column called "Protocol ID." I want to check a box if the protocol ID (the column to match) in the corresponding row is present in the other sheet (the column to search). The first image below is the one where I want a box checked, and the second image is the reference sheet to be searched for a matching protocol ID.

So far I've got: =IF(INDEX({Data Requests Protocol ID}, MATCH([Protocol ID]@row, {Data Requests Protocol ID}, 0)))..,

But I get stuck on how to tell Smartsheets that I want a check if the protocol ID is found and no check if the protocol ID is not found. Does anyone have thoughts?

Find more posts tagged with

Formulas

Accepted answers

All comments

Sameer Karkhanis

=IF(INDEX({Data Requests Protocol ID}, MATCH([Protocol ID]@row, {Data Requests Protocol ID}, 0)),1,0)

ejrbean

Thank you for the feedback, @Sameer Karkhanis !

Unfortunately, I tried that and got "invalid argument."

Maybe I'm taking the wrong approach... I'm thinking with an IF argument, I should have something that would be:

IF [Protocol ID]@row is anywhere in {Data Requests Protocol ID}, then 1; otherwise 0.

Is index match the correct argument to use?

ejrbean

I'm wondering if IFERROR could work, but I've never used that function before and am not sure how to apply it here. But the description of the function sounds like it could work.

ejrbean

I tried to write an IFERROR formula, but it doesn't seem to be working (it isn't checking a box that should be checked). Can someone tell where it is off?

=IFERROR(VLOOKUP([Protocol ID]@row, {Data Requests Protocol ID}, 1, 1), 0)

"Received requests" in the row with EA2174 should be checked (since it is present in the "Data Requests Protocol ID" column):

Can anyone tell what is off and/or have a better idea for what formula to use?

JessicaD

https://community.smartsheet.com/discussion/comment/324133#Comment_324133

I am running into a similar issue and istead of it being checked or unchecked it states No Match. Thoughts?

=IF(INDEX({Closed Jobs - Month End JOB NUMBER}, MATCH([Job Number]@row, {Closed Jobs - Month End JOB NUMBER}, 0)), 1, 0)

ejrbean

Hi @JessicaD

I ended up using COUNTIF instead of checking the box and referenced the column in the other sheet, and that has been working fine for me (it just says 0 if there aren't any matching values). This is the formula I used:

=COUNTIF({Data Requests Protocol ID}, [Protocol ID]@row)

So maybe you could do =COUNTIF({Closed Jobs - Month End JOB NUMBER}, [Job Number]@row)

Not sure if that would be helpful for your purposes, but it's working well for me!

Cybulski

If you did want to use the checkbox, you could do so building off your COUNTIF formula with an If function.

=If(COUNTIF({Data Requests Protocol ID}, [Protocol ID]@row)=0, "false", "true")

That will check the box if the answer is not 0 (and the value wasn't found) and leave it unchecked if it is 0 (and the value was not found).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.