Buzz
Today, Mytour will guide you on how to calculate payroll in Excel using Vlookup, Hlookup, Left, Right, Mid, and If. Feel free to explore the tutorial.
EXCEL PAYROLL CALCULATION USING VLOOKUP, HLOOKUP, LEFT, RIGHT, MID, AND IF FUNCTIONS
Excel Salary Calculation Exercises will familiarize you with proficient use of Vlookup, Hlookup, Left, Right, Mid, and If functions, enabling practical application in various scenarios.
For this exercise, we'll complete blank columns in the data table below based on two auxiliary data tables, using Vlookup, Hlookup, Left, Right, Mid, and If functions to achieve it.
Employee codes are formed by three factors: Classification (A, B, C, D), Years of service, and Department code.
Firstly, we'll use the Right and Hlookup functions to fill in the Department code.
Step 1: Enter the formula in cell D6 as follows: =HLOOKUP(RIGHT(B6,2),$A$16:$D$17,2,0).
Step 2: After entering the formula, press Enter to complete. Then, drag and drop the cell D6 down to let the system automatically fill in the remaining cells.
Next, we'll use the Left function to fill in the employee's Classification code.
Step 3: Enter the formula in cell G6: =LEFT(B6,1).
Step 4: Press Enter to complete, then hold and drag cell G6 down to fill in the remaining cells.
To fill in Years of service, we'll use the Mid function to extract the middle 2 characters from the Employee code.
Step 5: Enter the formula in H6 : =MID(B6,2,2)
Step 6: Press Enter to complete, then drag down to fill in the remaining cells.
Salary coefficient will depend on the Classification code and Years of service in the Coefficient Table. Here, we'll use the If statement with the Vlookup function for calculation.
Step 7: Enter the formula in cell I6: =VLOOKUP(G6,$F$18:$J$21,IF(H6>=16,5,IF(H6>=9,4,IF(H6>=4,3,2)))).
Step 8: Press Enter to complete, then drag down to fill in the remaining cells.
Finally, we'll calculate the Total salary by multiplying Basic salary * Workdays * Salary coefficient.
Step 9: Fill in cell J6 with the formula: =E6*F6*I6 then press Enter to complete. Drag down to fill in the remaining cells.
Here is a guide on how to calculate salary in Excel using Vlookup, Hlookup, Left, Right, Mid, and If functions. We hope that with this article, you can easily perform this Excel salary calculation exercise. If you encounter any difficulties, feel free to comment below, and Mytour's technical team will assist you.
Have you ever wondered how organizations, businesses, companies, etc., use payroll spreadsheets? Explore a sample payroll spreadsheet to clear up any confusion you may have.
From before to now, calculating percentages is a common practice, appearing in various aspects of life and frequently used in Excel. Read the article on various quick and accurate methods to familiarize yourself with this common type of math.
