How to Pull Values from Another Worksheet in Excel (6 Easy Ways)

In Excel operations, we need to retrieve data from time to time. Basically, the pulling values can be stored in the same worksheet or a different worksheet or workbook. Today, we are going to show you how to pull values from another worksheet in Excel. While fetching values from other worksheets, we need to consider that worksheets can be from the same workbook or different workbooks. So, we will show you how to pull values from another workbook in Excel. Furthermore, for this session, we are using Microsoft 365 version.

Download Practice Workbook

You can download the practice workbook from here:

6 Methods to Pull Values from Another Worksheet in Excel

Here, we will demonstrate six suitable methods to pull values from another worksheet in Excel. First things first, let’s get to know about the dataset that is the base of our examples.

Here, we have a table regarding movies, the table contains a bit of broader information about those movies. Additionally, we stored the table in the sheet named Details. Now, using this dataset, we will pull values across the other worksheets.

However, this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

1. Utilize Generic Formula with Cell Reference to Insert Values

Here, you can pull values from another worksheet by providing the cell reference followed by the sheet name in the formula. Actually, anything we write with an equal sign (=) is a formula.

You will understand it better through examples.

Here, we have introduced a column Actor to the worksheet named Generic Formula. Let’s say we want to pull the actor’s name for the movies from the worksheet named Details. Now, let’s explore the pulling method.

• First, all we need to do is provide the cell reference and the sheet name.

Here, Details is the sheet name and D5 is the cell reference. Furthermore, we must insert an exclamation “!” sign between the sheet name and cell reference. Basically, Excel differentiates sheet and cell reference through the “!” sign.

• Then, press ENTER.
• As a result, we found the actor’s name.

• After that, let’s do the same for the rest of the cells or double-click on the Fill Handle icon.

• Finally, we get the names of all actors.

Since our data is a limited one and are in the same sequence in both of the sheets, we get the names in the correct order.

2. Use VLOOKUP Function to Pull Values from Another Worksheet

Pulling or retrieving or fetching whichever name you mention, one function that may appear in your mind is the VLOOKUP function.

In the earlier section, we pulled the values using the cell references, but in the long run, it may not be useful. Here, this VLOOKUP can be the rescue there as it pulls values based on the match.

• Now, let’s write the formula using VLOOKUP in the sheet named VLOOKUP Function.

• Then, press ENTER to get the result.
• As a result, we have pulled the actor of the movie Jack Reacher from another sheet, Details.

• Then, double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D13.
• Lastly, we get the name of all actors.

However, in this case, if the data is not in the same sequence in both of the sheets, still we will get the correct names.

Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP

3. Combine Excel INDEX & MATCH Functions to Place Values

A well-known alternative to VLOOKUP is the combination of the INDEX and MATCH functions.

Basically, the MATCH function returns the position of a lookup value in a range and INDEX returns the value at a given location in a range.

Now, we will utilize this combination to fetch the genre of the movies.

• At this time, write the following formula in the D5 cell of the INDEX-MATCH sheet.

• Subsequently, press ENTER.
• Finally, we have pulled the genre value from the Details worksheet.

• Similarly, write the formula or exercise the AutoFill feature for the rest of the values.

4. Apply Excel XLOOKUP Functions to Insert Values from Another Worksheet

If you are using Excel 365, then you can use a function called XLOOKUP for pulling the values. Let’s pull the corresponding director’s name from the Details sheet.

• Firstly, write the following formula in the D5 cell of the XLOOKUP Function sheet.

• Secondly, press ENTER.
• As a result, we have pulled the director’s name from another sheet, Details.

• Then, do the same for the rest of the values.

5. Use Advanced Filter Feature for Pulling Values from Another Worksheet

In the previous method, you see the application of XLOOKUP function to pull values from another worksheet. Now, we will use the Advanced Filter option, where you don’t need to use any function.

Now, we want to pull all the values from the worksheet Details for the movie names.

• First, write down the criteria including that particular column header from where your criteria came. Here, you must write the criteria below the column header.

As an example, see the following image. Where we have written the names of the movies in the B5:B13 cells under the Movie column.

• Then, open the Advanced Filter option by clicking the Data tab > Sort & Filter > Advanced.

As a result, you will see a new dialog box named Advanced Filter.

• First, mark Copy to another location.
• Later, specify the range (Details!$B$4:$E$13) in the List range option from where you want to pull the values (worksheet named Details).
• Then, provide the criteria ($B$4:$B$13) in the Criteria range box.
• After that, choose the space ($B$15:$E$24) in the Copy to box. Here, you must select a space up to which you need.
• Subsequently, press OK.

• Finally, you’ll see the following output.

Read More: How to Import Data into Excel from Another Excel File (2 Ways)

Similar Readings

• How to Import Text File with Multiple Delimiters into Excel (3 Methods)
• VBA Code to Convert Text File to Excel (7 Methods)
• How to Extract Data from Excel Sheet (6 Effective Methods)
• Extract Filtered Data in Excel to Another Sheet (4 Methods)
• How to Extract Data from a List Using Excel Formula (5 Methods)

6. Employ VLOOKUP Function to Pull Values from Different Workbook in Excel

We may need to pull values from a worksheet from a different workbook.

To show you examples, we have copied the Details sheet value to another workbook called Use Another Workbook.xlsx. Furthermore, we have set the name of the sheet as Details in New Workbook.

And our summary table (worksheet name Summary) is still in the workbook Pulling Values from Another Worksheet.xlsx.

Now, we will pull the director’s name from a different workbook named Use Another Workbook.

Here, we can use any of the approaches (Cell Reference, VLOOKUP, INDEX-MATCH, XLOOKUP) we have mentioned in the earlier section. All you need to do is to provide the workbook name within the brackets.

For the time being, we are using the VLOOKUP function.

• Now, let’s write the formula.

• Then, press ENTER.
• Lastly, we have pulled the value, the director’s name, from a worksheet in another worksheet.

• Then, do the same for the rest of the values or exercise the AutoFill feature.

Read More: How to Pull Data From Another Sheet Based on Criteria in Excel

Practice Section

Now, you can practice the explained method by yourself.

Conclusion

We hope you found this article helpful. Here, we have explained 6 suitable methods to Pull Values from Another Worksheet in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Extract Data from Cell in Excel (5 Methods)
• Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
• How to Pull Data from Multiple Worksheets in Excel VBA
• Excel VBA: Pull Data Automatically from a Website (2 Methods)
• How to Extract Data from Excel Based on Criteria (5 Ways)
• Transfer Data from One Excel Worksheet to Another Automatically
• How to Extract Data From Table Based on Multiple Criteria in Excel