Using "Contains" or Wildcard to search for duplicates

I am currently using COUNTIFS to find and flag duplicate persons by a match of both last and first name with this formula:

=IF([Last Name]@row <> "", IF(COUNTIFS([Last Name]:[Last Name], =[Last Name]@row, [First Name]:[First Name], =[First Name]@row) > 1, 1), false)

I want to change it so that it flags fields containing the same first and last name. So for example, Jane Smith and Jane Smithson would be flagged as duplicates. I'm doing this because we are accepting records from users that sometimes have errors in them (an extra space after the name for example, or a hyphenated name where our other record only includes one part of it.) I want to catch those. What is the best way to do this?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!