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.

Great tutorial and alternatives!

Thanks for your feedback!

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?

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.