# Mapping Data from Another Sheet in Excel (6 Useful Methods)

Suppose you have the following two datasets.

### Method 1 – Link Cells for Mapping Data from Another Sheet in Excel

Steps:

• Insert an Equal (=) sign in an appropriate cell (F5 in this example).

• On the source dataset worksheet, click the appropriate cell (D5).

• Press Enter.

The cell should show the information from the source cell.

• Use the AutoFill tool to copy the link to the rest of the column.

• Format the linked column to match the rest of the sheet if necessary.

### Method 2 – Use the VLOOKUP Function to Map Data in Excel

Steps:

• Insert the following formula in the appropriate cell (F5 in our example).
`=VLOOKUP(B5,'Dataset 1'!B4:D9,3,FALSE)`

The Lookup Value is the cell that both worksheets have in common (B5).

The Table Array is the source data (‘Dataset 1’!B4:D9).

The Column_Index_Num is how many columns Excel has to count over to find the required information (3).

Exact match is False in this example.

• Press Enter.

• Use the AutoFill tool to copy the formula to the rest of the column.

### Method 3 – Pull Data from Another Sheet with INDEX-MATCH Function

Steps:

• Insert the following formula in the appropriate cell (G14).
`=INDEX('Dataset 1'!B5:D5,MATCH(\$F\$4,'Dataset 1'!\$B\$4:\$D\$4,0))`

The INDEX function returns the value, and the MATCH function helps to find the exact match from the source.

• Press Enter.

• Use the AutoFill tool to copy the formula to the rest of the column.

### Method 4 – Use the Advanced Filter to Map Data from a Different Sheet

Steps:

• Select the destination worksheet.
• Go to the Data tab and choose Advanced under the Sort & Filter group.

• An Advanced Filter window pops up.
• Choose the Copy to another location option.
• Input the appropriate range from the Dataset 1 worksheet into the List range box.

• Choose the Criteria range from the second sheet.

• Add the destination cell information in the Copy to box.

• Press OK.

### Method 5 – Use Excel VLOOKUP & INDIRECT Functions for Mapping Data

Suppose you have the following two datasets:

Steps:

• Open a new worksheet and put this formula in a blank cell (C5 in this example).
`=VLOOKUP(\$B5,INDIRECT("'"&C\$4&"'!\$B\$4:\$E\$9"),3,FALSE)`

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

• Press Enter.

• Use the AutoFill tool to copy the formula to the rest of the column.

### Method 6 – Map Data from Another Sheet with the HLOOKUP Function in Excel

Steps:

• Add the proper order to an empty cell beside the destination cells (F5:F9 in this example).

• Use this formula in the destination cell (F5).
`=HLOOKUP(\$F\$4,'Dataset 1'!\$B\$4:\$D\$9,HLOOKUP!U5+1,0)`

• Press Enter.

• Use the AutoFill tool to copy the formula to the rest of the column.

• Delete the order cells.

Get the sample file here and practice it by yourself.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun 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... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF