Match Problem

PYI

compared the column from one sheet to another sheet

Sheet1: ID

Sheet2 : EmpID

i can able to Match all the data which is under ID column to EmpID but if the ID Starts with 0 its not comparing. EX(0123)

I used this formula

=IF(COUNTIF({check1 Range 1}, EmpID@row) > 0, 1, 0)

Paul Newcome

The leading zero means the data is being stored as text instead of number. Insert a helper text/number column with the below formula in it to convert everything to text (everything must be the same data type) then reference this new helper column in your MATCH function.

=EmpID@row + ""

PYI

Hallo Mr.Paul,

Thank you for your reply, i tried out ,, but now both check boxes are empty..

Sheet1

Sheet2

I referenced sheet1 Helpercolumn and compared Helper2 Column

=IF(COUNTIF({check1 Range 1}, [Helper2]@row) = 1, 1, 0)

But Now both Checkboxes are empty ...

Paul Newcome

Double check your range. It should reference like so...

=IF(COUNTIF({check1 Helper column}, [Helper2]@row) = 1, 1, 0)

If the range is in fact correct, is it possible that those IDs are listed further down in the column? Your formula is only going to check the box if there is exactly one entry. If that ID is entered more than once, the box will no longer be checked.

PYI

Actually i just want to check if 11111 nr is present in EmpID then checkbox should be checked

PYU

ok,,, instead of count function i used match function, now all seems to fine😀,,,, but is it possible instead of #NO MATCH i just want to show checkbox

Thanks for your help

Andrée Starå

Hi @PYU

I hope you're well and safe!

Try something like this.

You'd use the IFERROR function.

=IFERROR(YourFormula, 0)

https://help.smartsheet.com/function/iferror

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

PYI

perfect!!😀 Thank you very much your help

Andrée Starå

@PYI

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!