I am trying to set up a spreadsheet to tabulate the results of a merit scoring period for a group of employees. Each supervisor returns a spreadsheet to me with several columns of data including employee name, job code and the merit score. Depending on the job code the score would be applied to one of seven different tables to define the category and wage increase. The tables are divided into three sections one each for Exceeds, Meets or Below Expectations, and the score is compared to the range for each scoring level. I have been able to set up a lookup to identify which table the job should be scored from, but my challenge has been to use that result to define the table which would then define their wage increase and category.
I have attached a stripped down spreadsheet that contains the spreadsheet the supervisors return on one tab, and the other tab contains the lookup for the table, and the tables themselves.
I tried nesting IF statements, but I know there is a limit of 7 deep and this table of scores may expand in the future as more jobs are added. A VBA solution is OK as I am beginning to learn some coding.
Thanks in advance for any assistance that can be provided.