How good would it be if cells get filled automatically? Most of the time we will love that. Today we are going to show you how to auto-populate cells in excel based on the value from another cell. For this session, we are going to use Excel 2019, feel free to use your preferred version.
First things first, let’s get to know about the dataset which is the base of our today’s examples.
Here we have a table that contains employees’ information like their name, id, address, respective department, and joining date. Using this data we will see how to populate cells automatically.
Note that this is a basic dataset with dummy data. in a real-life scenario, you may encounter a much larger and complex dataset.
You are welcome to download the practice workbook from the link below.
Auto Populate Cells Based on Another Cell
Here, we set our example in such a way that by providing an employee name, we will find his information automatically.
Here we have introduced information fields separated from the original table. Let’s say we set the Name, Robert.
Then we should get the details of Robert. Let’s explore how can we do that.
1. Using VLOOKUP Function
Just for a moment forget about “auto-populate” and think about retrieving data matching the criteria, which functions are coming into your minds? It’s quite obvious, VLOOKUP is one of those.
VLOOKUP looks for data, organized vertically. For further information, check this VLOOKUP article.
Now we are going to write a formula using the VLOOKUP function that will fetch the exact data we want in a cell.
Let’s write the formula for deriving the id of the employee
Within the VLOOKUP function, we have inserted the name (I4) as the lookup_value. Then the entire table range as the lookup_array.
The Employee ID is the 2nd column, so we have set 2 as the column_num.
We have used the IFERROR function to wrap up the VLOOKUP formula. This will eradicate any errors that arise from the formula (to know about the function, visit the article: IFERROR).
For deriving the department name, we need to modify the formula,
Here we have changed the column_num according to the position in the original table. The Department is the 3rd column, so we have used 3.
For the Joining Date and the Address, the formula will be
We have found the details for the employee. Now change the name and the cells will be auto-updated.
VLOOKUP with Drop-Down List
Earlier we provided the name manually. Sometimes it may seem time-consuming as well as confusing.
To solve the issue we can make a drop-down list for the employee name. Check the article to know about making a drop-down list.
In the Data Validation dialog box choose List and insert the cell reference of the names.
B4:B9 is the range that contains the names.
Now we will find the drop-down list.
We can choose the name more effectively and quickly now.
The other cells are being populated automatically as we used VLOOKUP.
2. Using INDEX – MATCH Function
The operation we have performed through VLOOKUP can be done alternatively. We can use the combination of INDEX-MATCH to populate the cells automatically.
The formula will be the following one
Here our formula derives the id number since we have provided the id range within INDEX and the MATCH function provides the row number, matching the criteria value in the table (B4:B9).
To derive the Department we will change the range in the INDEX and the formula will be the following one
Departments are in the range of D4 to D9.
The formula for the Joining Date will be
And for the address
Now to clarify, let’s erase the selection and select any of the names
You will find other cells get populated automatically.
3. Using HLOOKUP Function
If your data is oriented horizontally then you need to use the HLOOKUP function. To know about the function visit this article: HLOOKUP.
The Name field will be set from the drop-down list. And the rest of the field will be populated automatically.
For deriving id, we are going to use the following formula
The operation is similar to the VLOOKUP formula. Within the HLOOKUP function, we have provided the name as the lookup_value and the table as the lookup_array. The ids are at the 2nd row, so the row_num is 2. And 0 for the exact match.
Now, for the department, the formula will be
The Department is the 3rd row, so the row_num is 3 here.
Let’s write the formula for the joining date
The Joining Date is the 4th row, so the row_num is 4 here. Then for the address change the row number to 5.
Let’s erase the cells and select a name from the drop-down list
After selecting the name, you will find other cells are populating automatically.
4. INDEX-MATCH for Rows
We can also use the INDEX MATCH combination for the rows. The formula will be the following one
This is for deriving the id, so we have used C4:H4 in the INDEX function, which is the Employee ID row.
Change the row range to find the department
Similarly, change the row number for the joining date and address
Here C6:H6 is the Joining Date row.
And the C7:H7 is the Address row, so the formula for deriving the address will be like the one stated below
That’s all for today. We have listed several ways to auto-populate cells based on another cell. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here.