How to Auto Populate Cells in Excel Based on Another Cell

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.

Data - Auto Populate Cells In Excel Based On Another Cell

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.

Practice Workbook

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.

Criteria field - Auto Populate Cells In Excel Based On Another Cell

Here we have introduced information fields separated from the original table. Let’s say we set the Name, Robert.

Criteria Robert - Auto Populate Cells In Excel Based On Another Cell

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

=IFERROR(VLOOKUP($I$4,$B$4:$F$9,2,0),"")      

VLOOKUP Formula - ID - Auto Populate Cells In Excel Based On Another Cell

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,

=IFERROR(VLOOKUP($I$4,$B$4:$F$9,3,0),"")

VLOOKUP Formula - Department - Auto Populate Cells In Excel Based On Another Cell

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

=IFERROR(VLOOKUP($I$4,$B$4:$F$9,4,0),"")   

VLOOKUP Formula - Joining Date - Auto Populate Cells In Excel Based On Another Cell

And

=IFERROR(VLOOKUP($I$4,$B$4:$F$9,5,0),””)

VLOOKUP Formula - Address - Auto Populate Cells In Excel Based On Another Cell

We have found the details for the employee. Now change the name and the cells will be auto-updated.

Change of Name - Auto Populate Cells In Excel Based On Another Cell

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.

Data Validation - Auto Populate Cells In Excel Based On Another Cell

B4:B9 is the range that contains the names.

Now we will find the drop-down list.

Drop down list - Auto Populate Cells In Excel Based On Another Cell

We can choose the name more effectively and quickly now.

Change of Name - Auto Populate Cells In Excel Based On Another Cell

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.

MATCH locates the position of a lookup value in a row, column, or table. INDEX returns the value at a given location in a range. To know more visit the articles: INDEX, MATCH.

The formula will be the following one

=IFERROR(INDEX($C$4:$C$9,MATCH($I$4,$B$4:$B$9,0)),"")

INDEX-MATCH formula - ID - Auto Populate Cells In Excel Based On Another Cell

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

=IFERROR(INDEX($D$4:$D$9,MATCH($I$4,$B$4:$B$9,0)),"")

INDEX-MATCH formula - Department - Auto Populate Cells In Excel Based On Another Cell

Departments are in the range of D4 to D9.

The formula for the Joining Date will be

=IFERROR(INDEX($E$4:$E$9,MATCH($I$4,$B$4:$B$9,0)),"")

INDEX-MATCH formula - Joining Date - Auto Populate Cells In Excel Based On Another Cell

And for the address

=IFERROR(INDEX($F$4:$F$9,MATCH($I$4,$B$4:$B$9,0)),"")

INDEX-MATCH formula - Address - Auto Populate Cells In Excel Based On Another Cell

Now to clarify, let’s erase the selection and select any of the names

Select from drop list - Auto Populate Cells In Excel Based On Another Cell

You will find other cells get populated automatically.

Populated cell - Auto Populate Cells In Excel Based On Another Cell

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.

Row values - Auto Populate Cells In Excel Based On Another Cell

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

=IFERROR(HLOOKUP($C$11,$C$3:$H$7,2,0),"")

HLOOKUP Formula - ID - Auto Populate Cells In Excel Based On Another Cell

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

=IFERROR(HLOOKUP($C$11,$C$3:$H$7,3,0),"")   

HLOOKUP Formula - Department - Auto Populate Cells In Excel Based On Another Cell

The Department is the 3rd row, so the row_num is 3 here.

Let’s write the formula for the joining date

=IFERROR(HLOOKUP($C$11,$C$3:$H$7,4,0),"")

HLOOKUP Formula - joining date - Auto Populate Cells In Excel Based On Another Cell

The Joining Date is the 4th row, so the row_num is 4 here. Then for the address change the row number to 5.

=IFERROR(HLOOKUP($C$11,$C$3:$H$7,5,0),"")

HLOOKUP Formula - Address - Auto Populate Cells In Excel Based On Another Cell

Let’s erase the cells and select a name from the drop-down list

Drop down list - HLOOKUP Formula - ID - Auto Populate Cells In Excel Based On Another Cell

After selecting the name, you will find other cells are populating automatically.

Populated list - HLOOKUP Formula - ID - Auto Populate Cells In Excel Based On Another Cell

4. INDEX-MATCH for Rows

We can also use the INDEX MATCH combination for the rows. The formula will be the following one

=IFERROR(INDEX($C$4:$H$4,MATCH($C$11,$C$3:$H$3,0)),"") 

This is for deriving the id, so we have used C4:H4 in the INDEX function, which is the Employee ID row.

INDEX-MATCH formula row - ID - Auto Populate Cells In Excel Based On Another Cell

Change the row range to find the department

=IFERROR(INDEX($C$5:$H$5,MATCH($C$11,$C$3:$H$3,0)),"")

INDEX-MATCH formula row - Department - Auto Populate Cells In Excel Based On Another Cell

Similarly, change the row number for the joining date and address

=IFERROR(INDEX($C$6:$H$6,MATCH($C$11,$C$3:$H$3,0)),"")

Here C6:H6 is the Joining Date row.

INDEX-MATCH formula row - Joining Date - Auto Populate Cells In Excel Based On Another Cell

And the C7:H7 is the Address row, so the formula for deriving the address will be like the one stated below

=IFERROR(INDEX($C$7:$H$7,MATCH($C$11,$C$3:$H$3,0)),””)

INDEX-MATCH formula row - Address - Auto Populate Cells In Excel Based On Another Cell

Conclusion

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.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo