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

Excel Mapping Data from Another Sheet (6 Useful Methods)

Working in multiple worksheets is very common among regular excel users. But mapping data between those worksheets are 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.


Download Workbook

Get the sample file here and practice it by yourself.


6 Useful Methods for Mapping Data from Another Sheet in Excel

For illustration, here we have taken 2 datasets. The first one shows the information of order number, customer name and their countries of a company.

Excel Mapping Data from Another Sheet

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

Excel Mapping Data from Another Sheet


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 mapping data 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.

Link Cells for Mapping Data from Another Sheet in Excel

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

Link Cells for Mapping Data from Another Sheet in Excel

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

Link Cells for Mapping Data from Another Sheet in Excel

  • Finally, we successfully pulled data from another sheet.

Link Cells for Mapping Data from Another Sheet in Excel

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

Apply VLOOKUP Function to Map Data in Excel

Now we will map this data from Dataset 1 sheet.

Apply VLOOKUP Function to Map Data in Excel

Let’s follow the below steps:

  • First, insert the formula in cell F5.
=VLOOKUP(B5,'Dataset 1'!B4:D9,3,FALSE)

Apply VLOOKUP Function to Map Data in Excel

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.

Apply VLOOKUP Function to Map Data in Excel

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

Pull Data from Another Sheet with INDEX-MATCH Function

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.

Pull Data from Another Sheet with INDEX-MATCH Function

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

Use Advanced Filter to Map Data from Different Sheet

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

Use Advanced Filter to Map Data from Different Sheet

  • Next, insert the Criteria range from the second sheet.

Use Advanced Filter to Map Data from Different Sheet

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

Use Advanced Filter to Map Data from Different Sheet

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

Insert Excel VLOOKUP & INDIRECT Functions for Mapping Data

This is the other worksheet.

Insert Excel VLOOKUP & INDIRECT Functions for Mapping Data

Now follow the step-by-step guideline 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)

Insert Excel VLOOKUP & INDIRECT Functions for Mapping Data

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, you can see the Quantity of specific Order Numbers is shown in the table.

Insert Excel VLOOKUP & INDIRECT Functions for Mapping Data

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

Map Data from Another Sheet with HLOOKUP Function in Excel

  • Now, insert this formula in cell F5.
=HLOOKUP($F$4,'Dataset 1'!$B$4:$D$9,HLOOKUP!U5+1,0)

Map Data from Another Sheet with HLOOKUP Function in Excel

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

Conclusion

Concluding this article hoping 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. Follow ExcelDemy for more excel blogs.

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo