Calculation Issues

How to Calculate Employee Pay from Type, Days, and Grade in Excel

Huda Qurayshi Oct 10, 2026 869 views

Question details

The user needs to calculate total employee pay based on multiple criteria: employee type, grade level, and total days worked.

How to Calculate Employee Pay from Type, Days, and Grade in Excel

Product: Excel
Device & OS: not provided
Scenario: Payroll management where salaries vary based on an employee's grade (1-4) and whether they are stationed at headquarters or elsewhere.
Observed behavior: Requires a formula to dynamically look up the correct daily rate from a specific criteria and multiply it by the days worked to output total pay.

Before you start

Ensure that your employee data is cleanly organized in columns for Employee Type, Days Worked, and Grade, and that you have a defined set of daily salary rates for each grade and type.

Solution 1Recommended

Calculate Pay Using INDEX and MATCH with a Lookup Table

This method is highly recommended as it uses a dedicated lookup table, making it easy to update salary rates in the future without changing the formulas.

By building a separate table that holds your daily rates for each grade and employee type, you can use INDEX and MATCH to dynamically fetch the correct rate. The formula will find the intersection of the correct grade row and employee type column.

Create a salary rate lookup table

In an empty area of your sheet (e.g., cells H1:J5), set up a table with Grades in the rows (H2:H5) and Employee Types like 'Headquarter' and 'Non-Headquarter' in the columns (I1 and J1). Fill in the daily rates.

Select the output cell

Click on the cell where you want the calculated pay for the first employee to appear (for example, D2).

Enter the INDEX and MATCH formula

Type the formula: =INDEX($H$1:$J$5, MATCH($C2,$H$1:$H$5,0), MATCH($A2,$H$1:$J$1,0)) * $B2. This assumes C2 contains the Grade, A2 contains the Type, and B2 contains Days Worked.

Apply to all employees

Press Enter to calculate the first employee's pay. Then, click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of the list.

Calculate Pay Using INDEX and MATCH with a Lookup Table

Scalability: Using a separate lookup table allows you to quickly adjust pay rates for all employees by simply changing the values in the table, rather than rewriting formulas.

Solution 2

Calculate Pay Using IF and Inline Array Constants

This approach embeds the salary rates directly into the formula using arrays, which is useful for quick calculations without needing a separate lookup table.

Manage Payroll Efficiently

Calculate Complex Salary Structures with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup and reference formulas like INDEX and MATCH, making it incredibly easy to build dynamic payroll calculators without performance lags.

1. Open your data: Launch WPS Spreadsheet and open your employee payroll workbook.
2. Set up your reference table: Organize your daily salary rates into a clean reference table with grades and location types.
3. Insert the lookup formula: Use the built-in Function Library to easily insert and configure INDEX and MATCH formulas to calculate exact pay based on multiple conditions.
4. Save in Excel format: Save your completed workbook in .xlsx format to ensure full compatibility with colleagues using Microsoft Excel.

100% compatible with Microsoft Excel formulas and formattingSeamlessly handles array formulas and complex logical functionsFree to use with a lightweight, user-friendly interfaceBuilt-in templates for HR and payroll management

microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP instead of INDEX and MATCH for this calculation?

Yes, if you are using a newer version of Excel or WPS Spreadsheet, XLOOKUP can simplify this by searching the grade array and returning the corresponding value from the correct employee type array.

What if an employee works partial days?

As long as your 'Days Worked' column supports decimals (e.g., 5.5 days), the multiplication formula will accurately multiply the daily rate by the decimal value to calculate the prorated pay.

How do I handle more than two employee types?

If you have more than two types (e.g., Headquarter, Regional, Remote), the INDEX/MATCH lookup table method is ideal. Just add another column to your lookup table for the new type, and the MATCH function will automatically find the correct column based on the employee's designated type.

Excel & Data Problems