Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Do Data Mapping in Excel (5 Handy Ways)

Data Mapping is one of the first and essential steps for data management. In Microsoft Excel, you can easily do data mapping which reduces a lot of time and hassle in data management. This article demonstrates how to do data mapping in Excel in 5 handy ways.


Download Practice Workbook

You can download the practice workbook from the link below.


What Is Data Mapping?

Data mapping is the process of linking the data of one database to another. It is a very necessary step in data management. If you perform data mapping, after changing the data in one database, the data in another database will also change. This reduces a lot of time and hassle in data management.


5 Ways to Do Data Mapping in Excel

Microsoft Excel allows you to do data mapping in many ways. In the following stages of the article, we will see 5 ways to do data mapping in Excel.
We have used Microsoft Excel 365 version for this article, you can use any other versions according to your convenience.


1. Utilizing VLOOKUP Function to Do Data Mapping

In this method, we will see how to do data mapping in Excel utilizing the VLOOKUP function. Now, let’s assume you have a dataset with the Sales Quantity for three different models of laptops over several weeks. At this point, you want to extract the data for MacBook Air M1 in Week 3. Follow the steps below to do so.

Utilizing VLOOKUP Function to Do Data Mapping

Steps:

  • First, select the cell where you want your data. In this case, we select cell H6.
  • Then, insert the following formula in the cell.
=VLOOKUP(G6,B4:E12,2,FALSE)

Here, cell G6 is the cell indicating week no. for which we want our data. Also, range B4:E12 is the weekly sales dataset.

Utilizing VLOOKUP Function to Do Data Mapping

  • Finally, you will have your output as shown in the following screenshot.

Utilizing VLOOKUP Function to Do Data Mapping

Note: There are 3 other ways to do data mapping using the VLOOKUP function in Excel.

Read More: How to Map Data Using VLOOKUP in Excel (4 Quick Ways)


2. Using INDEX-MATCH Functions

Here, we will see how to do data mapping in Excel utilizing the INDEX-MATCH functions. Now, let’s assume you have a dataset with the Sales Quantity for three different models of laptops over several weeks. At this point, you want to extract the data for MacBook Air M1 in Week 3. Follow the steps below to do so.

Using INDEX-MATCH Functions

Steps:

  • First, select the cell where you want your data. In this case, we select cell H6.
  • Then, insert the following formula in the cell.
=INDEX(B4:E12,MATCH(G6,B4:B12),2)

In this case, cell G6 is the cell indicating week no. for which we want our data. Also, range B4:E12 is the weekly sales dataset.

Using INDEX-MATCH Functions

  • Finally, you will have an output as shown in the below screenshot.

Using INDEX-MATCH Functions


3. Linking Cells to Do Data Mapping in Excel

Now, we will link cells to do data mapping from another sheet. Suppose you have a dataset with the Sales Quantity for three different models of laptops over several weeks.

Linking Cells to Do Data Mapping in Excel

At this point, you are creating a datasheet and you want to link the data for sales quantity of Macbook Air M1 with the other sheet. Now, to do data mapping from another sheet follow the steps below.

Linking Cells to Do Data Mapping in Excel

Steps:

  • At the very beginning, select the first cell of the Sales Quantity column in the new worksheet. In this case, it is cell D6.
  • Next, insert the following formula into the cell.
='Linking Cells 1'!C6

Here, ‘Linking Cells 1’ is the name of the other worksheet from which we are mapping the data.

  • Then, drag the Fill Handle for the rest of the cells in the column.

Linking Cells to Do Data Mapping in Excel

  • Eventually, you will have an output as shown in the below screenshot.

Linking Cells to Do Data Mapping in Excel

Read More: Excel Mapping Data from Another Sheet (6 Useful Methods)


4. Applying HLOOKUP Function

In this method, we will see how to do data mapping in Excel utilizing the HLOOKUP function. Now, let’s assume you have a dataset with the Sales Quantity for three different models of laptops over several weeks. At this point, you want to extract the data for MacBook Air M1 in Week 3. Follow the steps below to do so.

Applying HLOOKUP Function 

Steps:

  • First, select the cell where you want your data. In this case, we select cell H6.
  • Then, insert the following formula in the cell.
=HLOOKUP(C5,C5:E12,4,FALSE)

Here, cell C5 is the cell indicating the laptop model we want our data for.

Applying HLOOKUP Function 

  • Finally, you will have your output as shown in the following screenshot.

Applying HLOOKUP Function 


5. Using Advanced Filter to Do Data Mapping in Excel

Now, suppose you want to find out data for an entire row from a table. You can easily do this in Excel by using the Advanced Filter feature in Excel. At this point, to do so, follow the below steps.

Using Advanced Filter to Do Data Mapping in Excel

Steps:

  • At the very beginning, insert Week and Week 3 as shown in the below screenshot. In this case, we insert Week and Week 3 in cells G4 and G5 respectively.

Using Advanced Filter to Do Data Mapping in Excel

  • Next, go to the Data tab.
  • After that, select Advanced from Sort & Filter.

Using Advanced Filter to Do Data Mapping in Excel

  • At this point, the Advanced Filter window will pop up.
  • Then, from that window select Copy to another location.
  • Next, in List Range insert the range you are extracting the data from. In this case, range $B$4:$E:$11 is the inserted range.
  • Now, insert range $G$4:$G$5 in the Criteria range.
  • After that, insert $G$7 in Copy to. Here, this is the cell where we will put the extracted data.
  • Consequently, click on OK.

Using Advanced Filter to Do Data Mapping in Excel

  • Eventually, you will have an output as shown in the below screenshot.

Using Advanced Filter to Do Data Mapping in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like below on the right side of every worksheet.

Practice Section


Conclusion

In this article, we have seen the 5 handy ways to do data mapping in Excel. Last but not least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Sowmik Chowdhury

Sowmik Chowdhury

Hello! Welcome to my Profile. I , Sowmik Chowdhury, graduated from the Bangladesh University of Engineering and Technology from the Department of Naval Architecture and Marine Engineering. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this..

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo