# If Match Check Box

Options
edited 12/09/19

How do I write a formula to check a box next to an ID Number if that same ID Number is also present in another column?  So Column 1 is ID Number to complete and Column 2 is ID Number completed list.  Thanks in advance!

Tags:

• ✭✭✭✭✭✭
Options

You could try something along the lines of...

=IF(COUNTIFS([Column 2]:[Column 2], [Column 1]@row) > 0, 1)

This will count how many times the ID number in Column 1 in whatever row the formula is in shows up in Column 2. If it shows up at all, it will check the box.

• Options

Thank you Paul.  I tried that and it's not checking boxes although I know I have matching numbers within the two columns.

• ✭✭✭✭✭✭
Options

Hi Summer,

Can you share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

Quick question... Are either Column1 or Column2 the Primary column? If not... Are the numbers for one column on the right and the numbers for the other column on the left? SS Sometimes stores what looks like numbers as actually a text string that just represents numbers. Text strings are automatically formatted to be left aligned while actual numbers are aligned to the right.

This may be the key as to why things aren't working because the formula I provided above SHOULD work.

• ✭✭
Options

@Paul Newcome You're formula worked, I've been looking at this for hours trying to figure it out. Such a simple formula even referencing a sheet range.

Thank you!

Melissa

• ✭✭✭✭✭✭
Options

@MCaputo Happy to help. 👍️

• ✭✭✭✭✭✭
Options

@Paul Newcome I have an interesting scenario I'm using this formula =IF(MATCH([Facilitator Name]@row, {first name}, 0), 1, 0) for a checkbox

The facilitator name is in sheet 1 and the first name column is in sheet 2

I want the checkbox to check if the names on sheet 1 & sheet 2 match with this formula. I received one row checked and the other rows now list #invalid data type even though names match on both sheet. Both Name columns are the primary column for each sheet so I wonder if that's the issue????

Not sure if this is the best formula for this situation either? I just need the checkbox column to check or remain unchecked if a name is not found on sheet 2.

Senior Program Coordinator

De Anza College

• ✭✭✭✭✭✭
Options

@Stacey Carrasco The MATCH function outputs a numerical value to represent where within the range the match was found. It is not a true/false type of function. That means if the name is the 5th one down then your IF statement is essentially reading:

=IF(5, 1, 0)

You can use the MATCH function, but you would need to say if MATCH is greater than zero or greater than or equal to one which would then change the IF to

=IF(5 >= 1, 1, 0)

• ✭✭✭✭✭✭
Options

@Paul Newcome ok, interesting so I changed around the formula to indicate > = 1 but it's still not working because it's checking off some boxes when the statement is false and should return #no match

=IF(MATCH([Facilitator Name]@row, {first name})>=1, 1, 0)

🤔

Senior Program Coordinator

De Anza College

• ✭✭✭✭✭✭
Options

@Stacey Carrasco Your new formula isn't specifying an exact match.

=IF(MATCH([Facilitator Name]@row, {first name}, 0)>=1, 1, 0)

• ✭✭✭✭✭✭
Options

@Paul Newcome that did the trick!!! 😂😁

thanks for seeing me through this one!!

Senior Program Coordinator

De Anza College

• ✭✭✭✭✭✭
Options
• ✭✭✭✭
Options

@Paul Newcome i'm using the same helpful formula as above but have my columns listed to be checked off monthly. Can i add another criteria to this formula? the month (or helper)?

=IFERROR(IF(MATCH([name]@row, {name}, 0) >= 1, 1, 0), " ")

I would need the column to be checked off when it matches name and month

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!