Lookups (2003)

[gvanhook]

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.

[HansV]

Can you provide a step by step explanation of how the result should be calculated for, say, the employee in row 14 of the Aquatics sheet?

[gvanhook]

Hans,

Step-by-step, the worksheet needs to look at the job code and determine which merit plan that job belongs to. Then it needs to use that merit plan and determine the employees rating and wage increase based on the score assigned by the supervisor.

It is amazing what taking your 5th break from a project can do. I found an error in one of the named ranges, and then realized that I had inverted the lookup table so my IF/VLOOKUP statements could not work. The spreadsheet now works the way I expected it to.

I am still challenged by the limitations of the method I am using (nesting IF statements). If my table of max scores increases by even one category my worksheet will fail. Is there a better way to accomplish what I have done here? I have attached the working spreadsheet for you to see.

[HansV]

Ah yes, the order of the lookup table was one of the things confusing me.

See the attached version. I have inserted an intermediate column; it can be hidden. The formulas now use MATCH, OFFSET and INDEX.