Excel Mapping Data from Another Sheet (6 Useful Methods)

Working in multiple worksheets is very common among regular Excel users. However, mapping data between those worksheets is extremely time-consuming work. Therefore, we need to know the method of mapping data from another sheet in Excel. Considering this, here is an article for you on Excel mapping data from another sheet with 6 useful methods.

Mapping Data from Another Sheet in Excel: 6 Useful Methods

For illustration, we have taken 2 datasets. The first one shows the information on order numbers, customer names, and the countries of a company.

The second one shows the product, quantity, and price with a similar order number.

As you can see both the datasets are interconnected based on their order number, we will map some data from the first dataset to the second one to get complete information at a time. Let’s go through the methods below:

1. Link Cells for Mapping Data from Another Sheet in Excel

Let’s begin to learn data mapping from another sheet in Excel with the simplest method. Here, we will link cells to map data. Follow the process below:

First, insert an Equal (=) sign in cell F5.

Then, go to the source dataset worksheet and click on cell D5.

Now, return to the previous worksheet, and you can see that cell F5 is already showing the information of linking with cell D5.
Next, press Enter.

Finally, we successfully pulled data from another sheet.

Following, use the AutoFill tool to get all the data of column D in the source dataset.

After that, follow the same process to insert the header title.
Finally, after some formatting, the final output looks like this.

2. Apply VLOOKUP Function to Map Data in Excel

In the second method, we will apply the VLOOKUP function to map data from another sheet in Excel. As you can see the data of the Country column is missing from the worksheet.

Now, we will map this data from Dataset 1 sheet.

Let’s follow the below steps:

First, insert the formula in cell F5.

=VLOOKUP(B5,'Dataset 1'!B4:D9,3,FALSE)

Here, B5 represents the Lookup Value because it is common in both worksheets. ‘Dataset 1’!B4:D9 illustrates the Table Array. It is basically the source worksheet name and cell range of it. After that, we inserted the Column_Index_Num as 3 because the required data column is the third column in Dataset 1 worksheet. Finally, for an exact match, type False.

Now, press Enter.
Finally, you can see a successful mapping of data with the VLOOKUP function.

Following, use the AutoFill tool to get all the data at once.
The final result looks like this:

3. Pull Data from Another Sheet with INDEX-MATCH Function

The combination of INDEX and MATCH functions is a very easy method to pull data from another sheet in Excel. Go through the steps below:

First, insert this formula in cell G14.

=INDEX('Dataset 1'!B5:D5,MATCH($F$4,'Dataset 1'!$B$4:$D$4,0))

Here, the INDEX function returns the value, and the MATCH function helps to find the exact match from the array of the source dataset.

Now, press Enter.
Here, you can see the required value is visible.

Finally, use the AutoFill tool to insert all the values.

4. Use Advanced Filter to Map Data from Different Sheet

Another useful method to map data from a different sheet is to use the Advanced Filter in Excel. Let’s see the process.

In the beginning, select your second worksheet where you will insert the result.
Here, go to the Data tab and choose Advanced under the Sort & Filter group.

Then, a new Advanced Filter window pops up.
In this window, mark the Copy to another location option.
After that, insert the List range from Dataset 1 worksheet.

Next, insert the Criteria range from the second sheet.

Lastly, insert the cell information in the Copy to box.

Then, press OK.
Finally, you can see that we got the data from the source worksheet.

5. Insert Excel VLOOKUP & INDIRECT Functions for Mapping Data

At this age, let’s insert the VLOOKUP and INDIRECT functions for mapping data. Here are two worksheets with similar titles but different values.

This is the other worksheet.

Now, follow the step-by-step guidelines for mapping data of these two worksheets.

First, open a new worksheet and insert this formula in cell C5.

=VLOOKUP($B5,INDIRECT("'"&C$4&"'!$B$4:$E$9"),3,FALSE)

Here, the INDIRECT function transforms the string into a name that Excel can understand and puts it in the table_array argument of VLOOKUP.

Now, press Enter.
That’s it, and you can see the Quantity of specific Order Numbers shown in the table.

Lastly, use the AutoFill tool, and you will get the result of the whole worksheet.

6. Map Data from Another Sheet with HLOOKUP Function in Excel

This final segment will teach you to map data from another sheet with the HLOOKUP function in Excel. Let’s see how it works:

First, insert the order beside the cell range F5:F9.

Now, insert this formula in cell F5.

=HLOOKUP($F$4,'Dataset 1'!$B$4:$D$9,HLOOKUP!U5+1,0)

Then, press Enter.
Finally, you have your desired data in the new worksheet.

Apply the Autofill tool to get the whole result.

Don’t forget to delete the orders afterward.

Download Workbook

Get the sample file here and practice it by yourself.

Mapping Data from Another Sheet.xlsx

Conclusion

Concluding this article, I hope that it was a helpful one to learn Excel mapping data from another sheet in 6 useful methods. Let us know your feedback on this article.

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

Get FREE Advanced Excel Exercises with Solutions!