💰Salary Appraisal Matrix (With Slabs)

Can you do Vertical (row with salary slabs) & Horizontal (column) Look-up at the same time in Excel? The Answer is Yes! Let us discuss how?

We may come across situations where we require the value which is an intersection of both Horizontal & Vertical look-up in which one criterion is in slabs. In simple words, we require the combination of LOOKUP, VLOOKUP & HLOOKUP in a single cell.

⚠️ Problem Statement

We have the Salary Appraisal Matrix of the company with us, with Salary slabs in the first column (vertical) and Performance grade in the row heading with Appraisal % in the Matrix form. We have to extract the Appraisal % for each individual depending on their existing pay scale and performance grade for the year under review.

There are two ways to solve the above scenario, which are

1. INDEX & MATCH Functions (Available on all Office suits)

2. XLOOKUP Function (Available only on M365 & Excel Web)

Note: M365 means Microsoft 365 Subscription based office suite.

Before looking into the solution, kindly check out my previous post which discusses the horizontal and vertical lookup:

Horizontal & Vertical Look-up 📄

😎 Bonus Tip (Name Manager)

Before analysing the look-up functions, let us first discuss the functionality of Name Manager. Using this feature we can name the specific range of cells and use it in the formulas with ease. This can be done via Name Box in the Formula bar or using the Defined Names group in the Formula tab.

For Instance, in the above problem statement, we can name the first column SLAB, first-row GRADE, and data table MATRIX.

For more detailed elaboration on this topic refer to this article

1️⃣ INDEX & MATCH

If you don’t have any idea about how INDEX & MATCH functions work (individually and in combined form), kindly check out this article.

Simple Explanation

1. INDEX function returns a value or the reference to a value from within a table or range using the relative position.

2. MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.

💡Solution

For looking up the Salary scale we are going to use 1 for match_type syntax in the MATCH function which will find the largest value that is less than or equal to lookup_value.

In the above example, the first MATCH function will lookup for the largest value that is less than or equal to the lookup value and returns its position of it (search for the value which is < 75,000 and returns the position of 70001 as 4), the second MATCH function will lookup for the position of grade C and returns the result as 3, and the INDEX function will return the value which is in the intersection of both the position within the MATRIX which is 10.50%.

2️⃣ XLOOKUP Function

The XLOOKUP function is gaining more traction in recent years because of its versatility & usage. For a more detailed elaboration of the syntaxes of the XLOOKUP function and the row & column lookup kindly check out this article.

In the above example, we have used two XLOOKUP functions one for looking up horizontal values (grades) inside the other one which looks up the vertical value (salary slab) in which we have mentioned -1 as match_mode which will lookup for the exact match or the next smaller item i.e., 70,001. In the end intersection of these two lookups will be the result which is 10.50%.