Excel tips: how to match salary to job class

Question

I have a table of pay levels, classifications and salaries. If I know the classification and salary, is there a formula that can work out the pay level?

Answer

You can use three functions in combination to achieve this unusual lookup. Usually, you know the classification and level and you want to work out the salary. If you have any two items, there are formulas that can extract the third item, providing the table is laid out correctly.

The table used is shown in Figure 1 in A1:F5. 

There are two things to note about this table layout. 

1. Levels increase from left to right.
2. Classification pay levels increase from top to bottom.

This layout is required by the functions we will use to solve the problem. The values represent the lowest salaries on each level. 

These techniques are reasonably advanced, and the companion video may assist if you are having trouble understanding how the formulas work.

Input: two out of three ain’t bad

There are three possible combinations of input - see rows 8, 9 and 10 in Figure 1. Yellow cells are input cells. Grey cells contain formulas. The formula in cell B9 provides the solution to this article’s question. The formula in cell C8 is the easiest to explain and also demonstrates the techniques, so I will start with it.

Typically, you use the classification and level to look up the salary (row 8 in Figure 1). The formula in cell C8 is:
=INDEX(B2:F5,MATCH(A8,A2:A5,0),MATCH(B8,B1:F1,0))

Syntax
INDEX(Range,Row_Number,Column_Number)

The Range in this case is the range containing all the salary values. The Row_Number and Column_Number arguments relate to the Range. The intersection of the Row_Number and Column_Number extracts a cell from the Range. INDEX displays the value of that cell. In the above formula, the Row_Number and Column_Number arguments are each defined by a MATCH function.

Note: In certain circumstances, you can omit the Row_Number or the Column_Number – but at least one must be used. These instances will be covered later in the article.

Syntax
MATCH(Value,Range,Match_Type)

The MATCH function returns a number that represents the position of the Value within a range. It is commonly used with INDEX functions to return the Row_Number, Column_Number or both.  Let’s review the first MATCH function in the formula. This returns the Row_Number.
MATCH(A8,A2:A5,0)

This matches the value in cell A8 (Officer) in the range A2:A5. This will return the number 2 since Officer is the second entry in that range. I have used 0 for the Match_Type, which specifies an exact match. This means if the value is not in the range, the #N/A error will be returned.
MATCH(B8,B1:F1,0)

This second MATCH function returns the Column_Number and it matches the entry from cell B8 (Lev 4) in the range B1:F1. It returns 4, because Lev 4 is the fourth entry in that range. If we replace the two MATCH functions with their results, we will have the following formula:
=INDEX(B2:F5,2,4) 

The INDEX function returns the intersection of the second row and fourth column in the range B2:F5. This is cell E3, which contains 65,000. The other two formulas will also use the combination of INDEX and MATCH functions. The OFFSET function will also be used to add flexibility to the formulas. I have covered OFFSET in recent articles, and the examples below again demonstrate its usefulness.

Note: The formulas that follow determine the missing element in an actual combination. Hence, entering 75,000 in cell C10 would be invalid, because there are no classifications where you can earn 75,000 on Level 3. An Officer on 75,000 would be on Level 5 and a Supervisor on 75,000 would be on Level 2.

Cell B9 contains the solution to this article’s question, and is: =INDEX(B1:F1,MATCH(C9,OFFSET(B1:F1,MATCH(A9,A2:A5,0),0)))

Optional Row_Number in the INDEX function – if the Range in the INDEX function is a single row, as above, then the Row_Number can be omitted. There is no Row_Number in this formula. The first MATCH function provides the Column_Number and this is sufficient to extract the level.

In Row 9 of Figure 1 we know the classification (Manager) and salary (87,000). Our formula needs to be flexible enough to select the correct salary range for the classification entered. The OFFSET function provides that flexibility by selecting the correct range for the first MATCH function to use to find the salary.

Syntax
OFFSET(Reference,Rows,Columns,Height,Width)

The Height and Width arguments are optional and are not used in any of the examples. Let’s review the OFFSET function from the above formula in isolation.
OFFSET(B1:F1,MATCH(A9,A2:A5,0),0)

The Reference, B1:F1, is the range that holds the level headings in row 1. From that Reference, we need to offset (move) a certain number of Rows to select the correct salary range. The MATCH function within the OFFSET function provides the number of Rows to move down the sheet.

The MATCH function is finding the Manager classification within the list of classifications in the range A2:A5. It will return 4 in this case, which offsets (moves) the B1:F1 range four rows down to B5:F5. The 0 at the end of the OFFSET means we offset zero columns, so we stay within columns B:F.

Because the OFFSET function provides the range B5:F5, the formula in cell B9 is in effect: =INDEX(B1:F1,MATCH(C9,B5:F5)) This is much easier to understand. Note: the MATCH function above only has the Value and the Range. 

The Match_Type has been omitted. When there is no Match_Type, Excel defaults to an approximate Match_Type. This means the value being searched for doesn’t have to appear in the Range. When this is used, the Range must be sorted in ascending order (either alphabetic or numeric depending on the data involved).

The MATCH function is finding the salary value (cell C9 contains 87,000) within the B5:F5 range. The approximate match means that since the salary value doesn’t exist in the range, Excel will return the largest value that is less than the value being searched for.

Since 86,000 (the value in cell C5) is the highest value in the range that is less than 87,000 and is the second cell in the range, the MATCH function will return 2.That means the INDEX function will return the second cell in the range B1:F1, which is Lev 2. The formula for cell A10 is:
=INDEX(A2:A5,MATCH(C10,OFFSET(A2:A5,0,MATCH(B10,B1:F1,0))))

This uses the same technique as the previous formula, but instead of looking across a row for a salary figure, it is looking down a column for a classification. This formula omits the Column_Number argument, since the range is one column wide.
OFFSET(A2:A5,0,MATCH(B10,B1:F1,0))

The OFFSET range A2:A5 is moved zero rows as it has ,0 immediately after the Reference. This time, the MATCH function provides the number of Columns to offset from A2:A5. In this case, it is moved 3 columns to the right, as Lev 3 is in the third cell in the range B1:F1. Hence the OFFSET function returns the range D2:D5. In effect, the formula in cell A10 is:
=INDEX(A2:A5,MATCH(C10,D2:D5))

This is also an approximate match, which requires that the salary values are ascending in this vertical range. The highest value that is in the range D2:D5 and is less than 63,500 is 63,000. This is in cell D3, the second cell in the range D2:D5, hence the MATCH function returns 2. The INDEX function will return the second entry in A2:A5, which is Officer.

These are advanced techniques, and it may take a few reads to understand how they work, especially if you haven’t used INDEX, MATCH or OFFSET before. The companion video and an Excel file may assist your understanding.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to various organisations. Questions can be sent to [email protected]