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

Method 1 – Inserting VLOOKUP Function to Do Data Mapping in Excel

Let’s assume 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:

Select the cell where you want your data. We selected cell H6.
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.

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.

Method 2 – Using INDEX-MATCH Functions for Data Mapping

Ley’s say you want to extract the data for MacBook Air M1 in Week 3.

Steps:

Select the cell where you want your data. In this case, we select cell H6.
Insert the following formula in the cell.

=INDEX(B4:E12,MATCH(G6,B4:B12),2)

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

You will have an output as shown in the below screenshot.

Method 3 – Linking Cells to Do Data Mapping in Excel

Suppose you have a dataset with the Sales Quantity for three different models of laptops over several weeks.

Let’s link the data for the sales quantity of Macbook Air M1 with the other sheet.

Steps:

Select the first cell of the Sales Quantity column in the new worksheet. In this case, it is cell D6.
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.

Drag the Fill Handle for the rest of the cells in the column.

You will have an output as shown in the below screenshot.

Method 4 – Applying HLOOKUP Function for Data Mapping

Suppose you have a dataset with the Sales Quantity for three different models of laptops over several weeks and want to extract the data for MacBook Air M1 in Week 3.

Steps:

Select the cell where you want your data. In this case, we chose H6.
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.

You will have your output as shown in the following screenshot.

Method 5 – Using Advanced Filter to Do Data Mapping in Excel

Suppose you want to find out data for an entire row from a table.

Steps:

Insert Week and Week 3 as shown in the below screenshot.

Go to the Data tab.
Select Advanced from Sort & Filter.

The Advanced Filter window will pop up. Select Copy to another location.
In List Range insert the range you are extracting the data from. Range $B$4:$E:$11 is the inserted range.
Insert range $G$4:$G$5 in the Criteria range.
Insert $G$7 in Copy to. This is the cell where we will put the extracted data.
Click on OK.

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?

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?

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?

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