Salary Increase Spreadsheet Help

Hello,

I am trying to develop a spreadsheet that will map salary increases based on 1) 5 columns (C-G), which each one representing a Salary band position (Example, colC = 1, colD = 2, etc...). This is the X axis. Then, 2) 5 Rows representing the performance grade for the year (Example, row1 = 1, row 2 = 2, etc...). This is the Y axis.

Each match on the grid will have a % meaning the percentage increase based on Band/Performance grade. So, for example, Salary Band 1, with a rating of 1 may = a 1.0% salary increase for the year, but Salary Band 1 with a rating of 3 may = a 2.5% increase.

Below all this will be a row for each Employee. One field will show their Salary band position number, and the field next to it will show their performance evaluation. I would like for their % salary increase to automatically be generated with only the Band and Peformance fields filled in based on the grid I described in the first paragraph. How can you do this, the easiest way, in Excel?

Please check out the attached spreadsheet as an example (I made up numbers for this example).

Thank you in advance!

Hi

Change your matrix slightly to be
A2: Performance %
A3-A7: 1,2,3,4,5
C1: Band Level
B2-F2: 1,2,3,4,5
B3:F3 contains your % increases.
D11: =INDEX($A$3:$F$7,MATCH(C11,$A$3:$A$7,0),MATCH(B11,$A$2:$F$2,0))

HTH

rylo

Hi

Copy sheet1!A10:D12, and paste it at sheet2!A10. Then put the above formula into sheet2!D11 and copy down.

rylo

Can you please look at the attached and see what I am doing wrong? I tried to follow your instructions so I know I am doing something wrong.

Thank you.

Hi

Using your example file then
D11: =INDEX($A$3:$F$7,MATCH(C11,$A$3:$A$7,0),MATCH(B11,$A$2:$F$2,0))

rylo

Rylo, that worked! Thank you so much!