Auto-populate cells in Excel

Question

As you can see in my screenshot, I have a table called "Payment Details Form" that needs to be filled and printed for each individual of 100 employees:

In another worksheet, I have a data table of every employee's salary details (called "Salary" table) containing Working Hours, basic salary, overtime, bonus and tax columns.

I have made a Data Validation list for the "Full Name" cell, and its source is the "Name" column of "Salary" table, so I can choose employee names from a drop down list.

I want a way that when I choose someone's name form the drop down list, Excel auto-populates other cells of "Payment Details Form" table according to the selected name and the corresponding data from "Salary" table.

Answer 1

If the Name column is the first one in the Salary table, you can use the VLOOKUP function to find the data you need to populate the form. For example, if the Name column is the first column of the table and the Basic Salary column is the fourth, you can use the following formula in cell J6 of the Payment Details form to return the Basic Salary of that employee.

=VLOOKUP(C6,Salary,4,FALSE)

The formula will look up the employee name (in cell C6) in the first column of the Salary table and return the corresponding value from the 4th column of that table.

If the Name column is not the first column of the table, you can use the MATCH function to find which row contains the employee name and the INDEX function to return the corresponding value of any other column.

For example, if the ID column is to the left of the employee name, you could use the following formula in cell C8 to return the employee's ID number.

=INDEX(Salary[ID],MATCH(C6,Salary[Name],0))

Where ID is the name of the column with employee IDs, and Name is the name of the column with employee names.

Note that I have assumed that your Salary table is an actual Excel Table. If it is just a range, you should replace Salary[ID] and Salary[Name] with the ranges that hold the IDs and Names.