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 365**, 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***, respective**

*address***, and**

*department***. Using this data we will see how to populate cells automatically.**

*joining date*Note that this is a basic dataset with dummy data. in a real-life scenario, you may encounter a much larger and more complex dataset.

**Download Practice Workbook**

You are welcome to download the practice workbook from the link below.

**2 Examples to Auto-Populate Cells Based On Another Cell in Excel**

Here, we set our example in such a way that by providing an ** employee’s name**, we will find his/her 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. ****Auto-Populate Cells Based On Another Cell in a Column-Wise Direction in Excel**

In this section, we will show **2 **ways of **auto-populating** cells depending on a value of a cell by searching for other values in a **column-wise** direction.

#### 1.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 mind? It’s quite obvious, **VLOOKUP** is one of those.**The VLOOKUP function** looks for data, organized vertically.

Now we are going to write formulas using **the VLOOKUP function** that will fetch the exact data we want in a cell.

** Steps**:

- Let’s write the formula in cell
**C14**for deriving theof the employee and press*ID***ENTER**.

`=IFERROR(VLOOKUP($C$13,$B$5:$F$10,2,0),"")`

Within **the VLOOKUP function**, we have inserted the name (** Robert)** as the

*lookup_value*. Then the entire table range

**$B$5:$F$10**as the

*lookup_array*.

The

**is the**

*Employee ID***column, so we have set**

*2nd***2**as the

*column_num*.

Finally, We have used

**the IFERROR function**to wrap up the

**VLOOKUP**formula. This will eradicate any errors that arise from the formula and give us a

**blank**for errors.

- For deriving the department name, write the following formula in cell
**C15**, and press**ENTER**.

`=IFERROR(VLOOKUP($C$13,$B$5:$F$10,3,0),"")`

Here we have changed the ** column_num** according to the position in the original table. The

**is the**

*Department***3rd**column, so we have used

**3**.

** **

- Similarly,
**ENTER**the following formula in cell**C16**for deriving the.*Joining Date*

`=IFERROR(VLOOKUP($C$13,$B$5:$F$10,4,0),"")`

The column number is **4 **for the position of the ** Joining Date **column.

- Lastly, type the following formula in cell
**C17**to get thefor*Address*and press*Robert***ENTER**.

`=IFERROR(VLOOKUP($C$13,$B$5:$F$10,5,0),"")`

In this way, we have found the details for the employee ** Robert**. Now change the name to

**and the cells will be auto-updated.**

*Paul*##### Adding 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.

- Select cell
**C13**where you want to create the**drop-down list**, and then go to the**Data**tab >>**Data Validation**group >>**Data Validation**.

- In the
*Data Validation*dialog box, choose**List**and insert the cell reference of the names, and press**OK**.

Here, **$B$5:$B$10 **is the range that contains the names.

Now we will find the **drop-down** list. We can choose the names more effectively and quickly now.

- Here, we have chosen
**Lina**.

Now, the other cells are being populated automatically as we used **VLOOKUP**.

#### 1.2. Applying INDEX-MATCH Functions

The operation we have performed through **VLOOKUP** can be done alternatively. We can use the combination of **INDEX-MATCH** to populate the cells automatically for the employee ** Robert**.

**The MATCH function**locates the position of a lookup value in a row, column, or table.

**The INDEX function**returns the value at a given location in a range.

** Steps**:

- Let’s write the formula in cell
**C14**for deriving theof the employee and press*ID***ENTER**.

`=IFERROR(INDEX($C$5:$C$10, MATCH($C$13,$B$5:$B$10,0)),"")`

__Formula Breakdown__

**MATCH($C$13,$B$5:$B$10,0) →**becomes**MATCH(“Robert”,$B$5:$B$10,0) → The MATCH function**will return the row index number where the information of the employee*Robert***Output → 5**

**INDEX($C$5:$C$10, MATCH($C$13,$B$5:$B$10,0)) →**becomes**INDEX($C$5:$C$10, 5) → The INDEX function**will return the**ID**corresponding to this employee in.*Row*6**Output → M001**

- If we got an error by executing
**the INDEX function**, then**the IFERROR function**will give us a**Blank**.

- To derive the
type the following formula in cell*Department***C15**and press**ENTER**.

`=IFERROR(INDEX($D$5:$D$10, MATCH($C$13,$B$5:$B$10,0)),"")`

Here, we changed the range inside **the** **INDEX function **to **$D$5:$D$10**.

- To have the
*Joining Date*insert the following formula in cell**C16**and press**ENTER**.

`=IFERROR(INDEX($E$5:$E$10,MATCH($C$13,$B$5:$B$10,0)),"")`

**$E$5:$E$10 **is the range of the ** Joining Dates**.

- Lastly, type the following formula in cell
**C17**to get thefor*Address*and press*Robert***ENTER**.

`=IFERROR(INDEX($F$5:$F$10,MATCH($C$13,$B$5:$B$10,0)),"")`

**$F$5:$F$10 **is the range of the ** Addresses**.

** **

Now to clarify, let’s erase the selection and select any other names like ** Alex**. Here, we created the

**drop-down**list following

**1.1**.

You will find other cells get populated automatically.

**2. ****Auto-Populate Cells Based On Another Cell in a Row-Wise Direction in Excel**

In this section, we will show **2 **ways of **auto-populating** cells depending on a value of a cell by searching for other values in a **row-wise** direction.

#### 2.1. Using HLOOKUP Function

If your data is oriented horizontally then you need to use **the HLOOKUP function**.

Here, we set the name of the employee from the **drop-down** list created by following **1.1**. And the rest of the field will be populated automatically when we will use **the HLOOKUP function**.

** Steps**:

- Let’s write the formula in cell
**D12**for deriving theof the employee and press*ID***ENTER**.

`=IFERROR(HLOOKUP($C$12,$C$4:$H$8,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 range

**$C$4:$H$8**as the

**. The**

*lookup_array***IDs**are at the

**2nd**row, so the

**is**

*row_num***2**. And

**0**for the

**.**

*exact match*- For deriving the department name, write the following formula in cell
**E12**, and press**ENTER**.

`=IFERROR(HLOOKUP($C$12,$C$4:$H$8,3,0),"")`

Here we have changed the ** column_num** according to the position in the original table. The

**is the**

*Department***3rd**row, so we have used

**3**.

- Similarly,
**ENTER**the following formula in cell**F12**for deriving the.*Joining Date*

`=IFERROR(HLOOKUP($C$12,$C$4:$H$8,4,0),"")`

The row number is **4 **for the position of the ** Joining Date **row.

- Lastly, type the following formula in cell
**H12**to get thefor*Address*and press*Robert***ENTER**.

`=IFERROR(HLOOKUP($C$12,$C$4:$H$8,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.

#### 2.2. Employing INDEX-MATCH Functions

We can also use the **INDEX MATCH** combination for searching different values for an employee throughout the rows.

** Steps**:

- Let’s write the formula in cell
**D12**for deriving theof the employee*ID*and press*Harry***ENTER**.

`=IFERROR(INDEX($C$5:$H$5,MATCH($C$12,$C$4:$H$4,0)),"")`

__Formula Breakdown__

**MATCH($C$12,$C$4:$H$4,0)****→**becomes**MATCH(“Harry”,$C$4:$H$4,0) → The MATCH function**will return the column index number where the information of the employee*Harry***Output → 1**

**INDEX($C$5:$H$5,MATCH($C$12,$C$4:$H$4,0)) →**becomes**INDEX($C$5:$H$5, 1) → The INDEX function**will return the**ID**corresponding to this employee in.*Column*1**Output → I001**

- If we got an error by executing
**the INDEX function**, then**the IFERROR function**will give us a**Blank**.

- To derive the
type the following formula in cell*Department***E12**and press**ENTER**.

`=IFERROR(INDEX($C$6:$H$6,MATCH($C$12,$C$4:$H$4,0)),"")`

Here, we changed the range inside **the** **INDEX function **to **$C$6:$H$6**.

- To have the
*Joining Date*insert the following formula in cell**F12**and press**ENTER**.

`=IFERROR(INDEX($C$7:$H$7,MATCH($C$12,$C$4:$H$4,0)),"")`

**$C$7:$H$7 **is the range of the ** Joining Dates**.

- Lastly, type the following formula in cell
**G12**to get thefor*Address*and press*Harry***ENTER**.

`=IFERROR(INDEX($C$8:$H$8,MATCH($C$12,$C$4:$H$4,0)),"")`

**$C$8:$H$8 **is the range of the ** Addresses**.

## Practice Section

For doing practice by yourself, we have provided a ** Practice **section on the right side of each sheet.

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