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.

We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

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.

1. Inserting VLOOKUP Function to Do Data Mapping in Excel

In this method, we will see how to do data mapping in Excel using 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.

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.

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

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

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

2. Using INDEX-MATCH Functions for Data Mapping

Here, we will see how to do data mapping in Excel using 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.

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.

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.

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

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.

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

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.

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.

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

4. Applying HLOOKUP Function for Data Mapping

In this method, we will see how to do data mapping in Excel using 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.

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.

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

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

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.

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.

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

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

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

Things to Remember

• Before performing extensive data mapping operations or making significant changes to your datasets, create backups of your data.
• Maintain documentation or a record of your data mapping process.
• Always test your data mappings with sample data to ensure the accuracy of the results.

Frequently Asked Questions

1. What are the advantages of using the INDEX-MATCH function for data mapping?

Ans: The INDEX-MATCH function is a powerful alternative to the VLOOKUP function for data mapping. It offers several advantages, including the ability to perform left-to-right lookups, handle unsorted data, and handle multiple criteria matching. It also provides more flexibility and is generally faster than the VLOOKUP function for large datasets.

2. How does the HLOOKUP function differ from VLOOKUP when it comes to data mapping?

Ans: While the VLOOKUP function is used to perform vertical lookups, the HLOOKUP function is specifically designed for horizontal lookups. It searches for a value in the first row of a dataset and retrieves a corresponding value from a different row. While both functions can be used for data mapping, their usage depends on the orientation of the datasets and the desired lookup direction.

3. How can linking cells help in maintaining dynamic data mapping in Excel?

Ans: Linking cells in Excel ensures that the mapped data stays connected and automatically updates when changes are made to the source or linked cells.

Practice Section

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

Download Practice Workbook

You can download the practice workbook from the link below.

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 in this article. If you have any queries, please drop a comment below.

Data Mapping in Excel: Knowledge Hub

• How to Do Data Mapping
• Map Data Using VLOOKUP
• Mapping Data from Another Sheet
• Create Mind Map from Data
• Save Excel File As XML Data Mapping

<< Go Back To Importing Data in Excel | Learn Excel