How to Build a Merit Increase Matrix in Excel

A merit increase matrix sets each employee's raise percentage from two things: their performance rating and where their pay sits in its range. In Excel you build the matrix as a small grid, look up each person's percentage with INDEX MATCH MATCH, and then calculate the increase, the new salary and the total cost. This guide is for HR, compensation and finance job seekers who need to build or check one. The percentages, salaries and bands below are made-up example figures to show the method, not pay recommendations.

What is a merit increase matrix?#

A merit increase matrix is a grid with performance ratings down the side and pay-range positions across the top. Each cell holds an increase percentage.

The position in the range is usually measured with the compa-ratio: salary divided by the range midpoint. A compa-ratio of 0.90 means 90% of the midpoint. The guide on how to calculate compa-ratio in Excel covers that step in detail.

Many matrices give a larger percentage to people lower in their range for the same rating, so pay moves toward the midpoint over time. Whether and how to do that is a policy decision for each organisation; Excel only applies the rules you set.

Step 1: Lay out the matrix#

Put ratings in column A and the compa-ratio bands in row 1. The key detail: write each band as its lowest value, as a real number, in ascending order:

So column B means "below 0.90", column C means "0.90 up to 1.10", and column D means "1.10 and above". For readers, add a label row above the matrix such as "Below 0.90 | 0.90–1.09 | 1.10+", but keep the formulas pointing at the numbers in row 1.

Type the percentages as numbers (0.05 or 5%) and format them with Ctrl+Shift+% (Mac: ⌃⇧%). Text such as "5 %" won't calculate.

Step 2: Add each employee's compa-ratio#

Put the employee list below the matrix, with headers in row 11 and Ana in row 12. Column A holds the name, B the rating, C the salary, D the range midpoint and E the compa-ratio, which is =C12/D12:

Keep the full value in the cell (for Ana, 0.92857…) and only change the display. Rounding the compa-ratio itself can move someone across a band edge.

Step 3: Look up the increase with INDEX MATCH MATCH#

INDEX returns a cell from the grid; the two MATCH functions tell it which row and which column. With the rating in G1 and the compa-ratio in G2:

=INDEX(B2:D6,MATCH(G1,A2:A6,0),MATCH(G2,B1:D1,1))

1. MATCH(G1,A2:A6,0) finds rating 4 in the rating column: row 2 of the grid. The 0 means exact match.
2. MATCH(G2,B1:D1,1) finds the band for 0.96. The 1 means approximate match: the largest value that is not above 0.96, which is 0.90, column 2.
3. INDEX(B2:D6,2,2) returns 4%.

Approximate match only works if the band minimums are sorted from smallest to largest. A value exactly on an edge belongs to the higher band: Dev's 0.90 lands in the 0.90 column and gets 3%. The INDEX function guide and the MATCH function guide cover both arguments, and INDEX MATCH MATCH explained goes deeper into two-way lookups.

In the employee table, point the formula at each row's own cells and lock the matrix with $, for example =INDEX($B$2:$D$6,MATCH(B12,$A$2:$A$6,0),MATCH(E12,$B$1:$D$1,1)), then fill down (Windows: Ctrl+D; Mac: ⌘D).

Step 4: Calculate the increase and the new salary#

With the percentage in column F, the money is simple multiplication. In G12 and H12:

=ROUND(C12*F12,0)
=C12+G12

The first formula is the increase amount, rounded to whole units; the second is the new salary. The results for the example team:

If you only need the new salary, =C12*(1+F12) does it in one step. The guide to increasing a number by a percentage covers this pattern and its common slips.

Step 5: Check the matrix against the budget#

A matrix is usually designed to fit a total budget, so check it every time you change a percentage. Two formulas do it:

=SUM(G12:G17)
=SUM(G12:G17)/SUM(C12:C17)

For the example team, the increases total 12,232 on salaries of 408,300, an average increase of 3.0%. If the budget is a different figure, adjust the percentages in the matrix and every lookup updates.

Note that this is a weighted average: higher salaries count for more. A simple =AVERAGE(F12:F17) of the percentages gives about 3.1% here, which is not the cost to the budget.

Other ways to look up the matrix#

XLOOKUP can do the same two-way lookup in Microsoft 365 or Excel 2021 and later. The inner XLOOKUP picks the band column with match mode -1; the outer one picks the rating:

=XLOOKUP(G1,A2:A6,XLOOKUP(G2,B1:D1,B2:D6,,-1))

This also returns 4%. The guide to two-way lookups compares the options. Whichever you use, watch for these errors:

1. #N/A from the band MATCH. The compa-ratio is below the first band. Start the first band at 0.
2. #N/A from the rating MATCH. The rating is text in one table and a number in the other.
3. The wrong band. The header row isn't sorted, or holds text like "0.90–1.09" instead of numbers.
4. Unlocked ranges. Without $, the matrix shifts as you fill down.

Merit matrices sit alongside other HR work such as rosters and pay runs; see the Excel skills for HR jobs and the payroll calculator guide.

Practise it#

The heart of a merit matrix is a two-way lookup. Practise INDEX and MATCH in a live spreadsheet, then calculate increases and new salaries in the percentages and percent change lesson. When you are ready, the HR roster interview drill puts lookups and percentages together in a realistic task. Each lesson checks your work instantly.