extrapolate full social security based on last 4 digits

[y3tter]

I have an employee worksheet that I am using to get open enrollment processed. Our insurance provider provided us with a a sheet with employee names and their redacted social security number(ex xxx-xx-1234). I am needing to have the full SS number displayed. I have a another sheet that lists employee names and their full SS number. How can I do a lookup to match the last four digits of the redacted number to and return the full SS number?

 

[Joe4]

Yes, data was sorted by last 4 digits and then reviewed for duplicates. Scott T's formula was the best option due to not every family falling into the traditional model(stepchildren, unmarried, children with different address). Joe4 scared me with his reminder of the birthday paradox. Apparently social security numbers aren't completely random and rely on dob, place of birth, etc. Still, I was probably lucky to not have witnessed any duplications.

Unless you population is set in stone, and is never changing, I would recommend adding that second layer of checking. You really don't want to be satisfied with a "works for now" solution that can easily break based on something that is likely to happen at some point in the future. Better to be prepared (especially when dealing with personal information and legal stuff may come into effect).

 

[Scott T]

At the very least it would be a good idea to add something to check if any new employee creates a duplicate last 4 condition.