Lesson#88: How to make a Payslip format from Employee database

So my friend, I will here discuss an important lesson which are really required for office people. That is how to make a salary slip from an employee database. This is not about salary slip only, we can make many similar type of formats using this method.

I am having a employee database in excel format which I have shown in above picture. Now in another sheet I will make a salary slip format and I link these two sheets.

Here in another sheet I have made a salary slip format just like above picture.

There I will VLOOKUP all the other fields from an unique value. That unique value in my example is the employee code. So I will make a dropdown list of employee code by Data Validation (List).

So selected the cell beside employee code then I have to go to Data>Data Validation

There I have to choose List and select the source.

See in the above image, I have selected the source and clicked OK.

Now my dropdown list of Employee Code is ready.

Now from the Lookup Value (Employee Code) I will VLOOKUP all the other fields. Copied and pasted in the other cells and just changed the column number for ease of work.

If you are fetching some difficulties for finding the column number then you should number all headers as I did in above picture.

Just see the above picture, I have looked up value for every necessary fields.

And I did looked up the value for salary breakup amount also using VLOOKUP. So the primary role plays the VLOOKUP function.

If your unique id column is in the middle of table then you can’t use VLOOKUP, then you have to use INDEX+MATCH combination to lookup value.

then you should go through this post

Lesson#41: Combination of INDEX and MATCH function is better than any other lookup function

Download the sheet here which is shown in post

Puspendu is the founder author of Excelabcd. He is a creative person, blogger and Excel-maniac guy.

Tagged with: ,

Leave a Reply

Your email address will not be published. Required fields are marked *