Check box if matching value in other sheet

ejrbean

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?

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!