How to Auto-Populate Cells Based On Another Cell in Excel

Get FREE Advanced Excel Exercises with Solutions!

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, address, respective department, and joining date. Using this data we will see how to populate cells automatically.

auto populate cells in excel based on another cell

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.

examples to 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.

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.

Applying VLOOKUP function to auto populate cells in excel based on another cell

Steps:

  • Let’s write the formula in cell C14 for deriving the ID of the employee and press 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 Employee ID is the 2nd column, so we have set 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.

ID

  • 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 Department is the 3rd column, so we have used 3.

Dept

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

Date

  • Lastly, type the following formula in cell C17 to get the Address for Robert and press ENTER.
=IFERROR(VLOOKUP($C$13,$B$5:$F$10,5,0),"")

Applying VLOOKUP function to auto populate cells in excel based on another cell

In this way, we have found the details for the employee Robert. Now change the name to Paul and the cells will be auto-updated.

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.

Drop-down list to auto populate cells in excel based on another cell

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

Drop-down list to auto populate cells in excel based on another cell


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.

Applying INDEX-MATCH functions to auto populate cells in excel based on another cell column-wise

Steps:

  • Let’s write the formula in cell C14 for deriving the ID of the employee and press 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 Department type the following formula in cell 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.

Applying INDEX-MATCH functions to auto populate cells in excel based on another cell column-wise

  • Lastly, type the following formula in cell C17 to get the Address for Robert and press 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.

Applying INDEX-MATCH functions to auto populate cells in excel based on another cell column-wise


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.

Applying HLOOKUP function to auto populate cells in excel based on another cell

Steps:

  • Let’s write the formula in cell D12 for deriving the ID of the employee and press 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 lookup_array. The IDs are at the 2nd row, so the row_num is 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 Department is the 3rd row, so we have used 3.

Applying HLOOKUP function to auto populate cells in excel based on another cell

  • 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 the Address for Robert and press 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.

Applying HLOOKUP function to auto populate cells in excel based on another cell


2.2. Employing INDEX-MATCH Functions

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

Applying INDEX-MATCH functions to auto populate cells in excel based on another cell row-wise

Steps:

  • Let’s write the formula in cell D12 for deriving the ID of the employee Harry and press 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 Department type the following formula in cell 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.

Dept

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

Applying INDEX-MATCH functions to auto populate cells in excel based on another cell row-wise

  • Lastly, type the following formula in cell G12 to get the Address for Harry and press 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.

practice


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.


Further Readings

Shakil Ahmed

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.

4 Comments
  1. Great tutorial and alternatives!

  2. 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 Avatar photo
      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo