How do I automate a cross check between two Employee Name columns that are formatted differently?

kebern

I've inherited a large set of sheets that were set up to pull in employee info based on an employee ID number input via form. There is an INDEX:MATCH to display the employee's name, supervisor, position, etc. from an agency roster based on the ID number.

Because some employees mistype their ID number, the wrong info (or none at all) may be displayed. Someone before me added a text column for the form user to enter the employee's name in the hopes of setting up a cross check between this field and the name brought in by the ID number.

The agency roster has names formatted as "LAST, FIRST"; of course we are mostly getting people submitting "FIRST LAST" via the form field (as well as other variables such as nicknames and misspellings) eg:

(I've added helper text asking the form user to use "LAST, FIRST"—though this just means some will follow this and some won't...) I realize we could move to one field for first and one field for last name, but we would still get some discrepancies, e.g. Josh v.s. Joshua.

What is the simplest way to build an actual cross check between two Employee Name columns, perhaps looking for a partial text match, so that we can set up a workflow to notify someone when there is a mismatch? Do I need helper columns + formulas to separate first and last name and then something like nested COUNTIF/CONTAINS?

Thank you!

kebern

In case it helps anyone in the future, I thought I would mention we switched from using Employee ID to Employee Email. Of course that still had some user input errors (though fewer!), mostly because our agency provides both .org and .k12.or.us email addresses to employees. To address this I built in a helper column/formula to extract the "prefix" of the email address and disregard the "@…". This has dropped our error rate significantly.