If Match Check Box

SummerEdwards

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!

Paul Newcome

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.

SummerEdwards

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

Andrée Starå

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

Paul Newcome

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.

MCaputo

@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

Paul Newcome

@MCaputo Happy to help. 👍️

Stacey Carrasco

@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.

Paul Newcome

@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)

Stacey Carrasco

@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)

🤔

Paul Newcome

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

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

Stacey Carrasco

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

thanks for seeing me through this one!!

Paul Newcome

@Stacey Carrasco Happy to help. 👍️

Natalia

@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

Stacey Carrasco

@Paul Newcome Can you help explain why this formula, returns a #no match in the cell instead of leaving the check box blank?

=IF(MATCH([Email(s)]@row, {HS Reg emails}, 0) >= 1, 1, 0)

I know I have this formula someplace else but for some reason I can't seem to locate. 🤦‍♀️😁