solution for non-technical user uploads to SQL Server DB?
There are lots of tools and methods available for loading data. But the easier you want this to be for end users the more complex and sophisticated your dataloading routine will become.

Data validation and business rules need to be defined and implemented. Somebody has to do it. No software is smart enough to do that "auto-magically".

You could build data checking into the spreadsheet. Hopefully this would stop errors before you load. It is possible to import data directly from a db query and there is a variety of lookup and validation tools in excel.

You could build an import routine in ssis  and apply validations, filters, lookups and substitutions as part of the import process. It can even handle errors and redirect invalid records back to the user. But this takes time and effort.

My preferred approach would be to use a db to begin with rather than excel. That way datatypes, dependencies, rules and constraints can be enforced from the start. You could develop an interface in MS-access. 

Unfortunately all of these things take time. The more sophisticated you want the app to be more time it will take to develop.