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 the Microsoft 365 version.

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

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.

=Details!D5

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.

=VLOOKUP(B5,Details!$B$5:$E$13,3,0)

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

Formula Breakdown

Here, we have provided B5 as the lookup_value within the VLOOKUP function, and Details!$B$5:$E$13 is the lookup_array. You can notice we have provided the sheet name before the range. Moreover, the sheet name and range are separated by the “!” sign.
Then, 3 is the col_index_num because actors are in the 3rd column of the range.
Lastly, 0 for the exact match.

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.

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.

=INDEX(Details!$C$5:$C$13,MATCH(B5,Details!$B$5:$B$13,0))

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

Formula Breakdown

Here, within the MATCH function, B5 is the lookup_value, and Details!$B$5:$B$13 is the lookup_range.
Then, this MATCH portion provides the position.
And then INDEX pulls the value from Details!$C$5:$C$13 range.

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 to pull 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.

=XLOOKUP(B5,Details!$B$5:$B$13,Details!$E$5:$E$13,"Not Found")

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

Formula Breakdown

Here, B5 is the lookup_value, Details!$B$5:$B$13 is the lookup_range, and Details!$E$5:$E$13 is the range from which we need to pull values. Furthermore, you can notice we have written the sheet name, Details, prior to each of the ranges.
In addition, we have added “Not Found” to the optional field if_not_found.

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

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.

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.

=VLOOKUP(B5,'[Use Another Workbook.xlsx]Details in New Workbook'!$B$4:$E$13,4,0)

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

Formula Breakdown

Here, prior to the cell range $B$4:$E$13 we have provided the sheet name (Details in New Workbook) and the workbook name (Use Another Workbook.xlsx). Additionally, the workbook name is in the brackets.
After that, the range is separated by these two by a “!” sign.
Since we need to count the workbook and worksheet simultaneously, so they are within single quotes (‘’).