Match Problem

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)
Answers
-
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 + ""
-
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 ...
-
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.
-
Actually i just want to check if 11111 nr is present in EmpID then checkbox should be checked
-
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
-
Hi @PYU
I hope you're well and safe!
Try something like this.
You'd use the IFERROR function.
=IFERROR(YourFormula, 0)
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!
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.
-
perfect!!😀 Thank you very much your help
-
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!
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 146 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 73 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives