# 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. 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. 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

`=IFERROR(VLOOKUP(\$I\$4,\$B\$4:\$F\$9,2,0),"")      ` 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),"")` 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),"")   ` And

=IFERROR(VLOOKUP(\$I\$4,\$B\$4:\$F\$9,5,0),””) 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.

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)),"")` 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)),"")` 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)),"")` And for the address

`=IFERROR(INDEX(\$F\$4:\$F\$9,MATCH(\$I\$4,\$B\$4:\$B\$9,0)),"")` 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

`=IFERROR(HLOOKUP(\$C\$11,\$C\$3:\$H\$7,2,0),"")` 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),"")   ` 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),"")` 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),"")` 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

`=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. Change the row range to find the department

`=IFERROR(INDEX(\$C\$5:\$H\$5,MATCH(\$C\$11,\$C\$3:\$H\$3,0)),"")` 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. 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)),””) ## 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.

1. Reply Great tutorial and alternatives!

• Reply Thanks for your feedback!

2. Reply what if the name is the same though? I have a list of names and addresses but unfortunately some of the names are identical (and has to be), how can I do this then?

• Reply Naimul Hasan Arif Aug 14, 2022 at 12:21 PM

In case of making some effective lookup, every input should be unique (unless you are in any particular case) to get the related information. If you have some similar names, let’s assume some Employee Names, then use other particulars as input like Employee ID. If you don’t have this type of unique number (ID) column you can create it quite easily, for assistance do check https://www.exceldemy.com/excel-auto-generate-number-sequence/, then apply the lookup. I hope this will solve your problem. 