Download Here: Find the Midpoint Most Closely Aligned to the Market Value
Each year, after I have updated all of my market data with the latest salary surveys, I test the market values of my benchmarked jobs against the midpoint of the grade they are currently in to determine if any jobs are more closely aligned to another grade. This practice is especially useful for identifying “hot jobs” that have grown significantly in market value in the course of a year or two, depending on how often you refresh your salary survey data.
You could use a VLOOKUP to attempt this, but you would find that the data will not be as accurate and you will have errors. If you aren’t a fan of errors, then we need to use an index/match formula, an array formula which looks like this:
Getting Started with the Formula
Here’s the formula that we’ll be using to find the midpoint that is closest to our market value:
Or if you want to copy and paste it directly from this article:
=(INDEX(‘Salary Ranges’!$C$2:$C$34,MATCH(MIN(ABS(‘Salary Ranges’!$C$2:$C$34-C2)),ABS(‘Salary Ranges’!$C$2:$C$34-C2),0),)
I’ll admit, when I first saw the formula, I thought it was pretty intimidating, until I realized that in reality, the formula is really pretty simple, and only has two data ranges that need to be defined:
- The range of midpoints, and
- The market value
If you set your data up similarly to the sample workbook, you can even copy and paste the formula into any spreadsheet, and then select your data ranges by simply dragging the highlighted cells.
And like so:
If you are not familiar with the array formulas, such as this, please know that you must hit Control + Shift + Enter to place the formula between braces. This is required for all array formulas.
In the example workbook, I have a list of jobs with their corresponding market values, and a column (the bright orange column) with the formula that will return the midpoint closest to the market value.
And on another tab, I have the salary ranges:
And that’s all there is to it to find the midpoint that corresponds with the market data. Just four easy steps:
- Copy the formula into your workbook
- Set the market value cell
- Set the midpoint range
- Hit Control + Shift + Enter
The final step to finding the grade is pretty easy. It’s just wrapping a VLOOKUP around the formula that we just created:
=VLOOKUP((INDEX(‘Salary Ranges’!$C$2:$C$34,MATCH(MIN(ABS(‘Salary Ranges’!$C$2:$C$34-C2)),ABS(‘Salary Ranges’!$C$2:$C$34-C2),0),), ‘Salary Ranges’!C:G,5,0)
In order to make this work, we need to update our salary range table slightly, since we are performing a VLOOKUP on the midpoint, we need to have a column with the salary grade name to the right of the midpoint. Like the bright blue column on the left, below:
Now our VLOOKUP formula to find the grade should be pulling the value in the right-most column, showing us exactly which grade has a midpoint most closely aligned to the market value.