How to Pull Values From Another Worksheet in Excel

In Excel operations, we need to retrieve data from time to time. 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. For this session, we are using Excel 2019 (and a bit of Excel 365), feel free to use yours.

First things first, let’s get to know about the dataset that is the base of our examples.

Data - How to Pull Values From Another Worksheet Excel

Here we have two tables regarding movies, one table has the summary of the movie where the other one contains a bit of broader information. We stored the tables in two different sheets Summary and Details. Using this dataset, we will pull values across the worksheets.

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

Practice Workbook

You are welcome to download the practice workbook from the link below.

Pull Values From Another Worksheet

While fetching values from different worksheets, we need to consider that worksheets can be from the same workbook or different workbooks.

1. Pull Values From Another Worksheet Within the Same Workbook

I. Straight Forward Pull with Cell Reference

You can pull values from another worksheet by providing the cell reference followed by the sheet name in the formula. Anything we write with equal sign (=) is a formula.

You will understand it better through examples. Let’s say we want to pull the actor’s name for the movies.

Pull Actor data from Details sheet

Here we have introduced a column Actor to the movie summary table. Now, let’s explore the pulling method.

All we need to do is to provide the cell reference along with the sheet name.

=Details!D4

Cell reference to pull values

Here Details is the sheet name and D4 is the cell reference. We need to insert a “!” sign between the sheet name and cell reference. Excel differentiates sheet and cell reference through the “!” sign.

Result of using cell reference to pull values from sheet

We have found the actor’s name. Let’s do the same for the rest of the cells or utilize the AutoFill feature.

Pull values from Details sheet using VLOOKUP

We get the name 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.

II. Pull Values Using VLOOKUP

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

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

Let’s write the formula using VLOOKUP

=VLOOKUP(B4,Details!$B$4:$E$12,3,0)

VLOOKUP Formula to Pull Values From Another Worksheet

Here we have provided B4 as the lookup_value within the VLOOKUP function and Details!$B$4:$E$12 is the lookup_array. You can notice we have provided the sheet name before the range. And the sheet name and range are separated by the “!” sign.

Here 3 as actors are in the 3rd column of the range and 0 for the exact match.

VLOOKUP Formula result - Pull Values From Another Worksheet

We have pulled the actor of the movie Jack Reacher from another sheet, Details. Write the formula for the rest of the values or exercise the AutoFill feature.

Pull values from Details sheet using VLOOKUP

III. Pull Values Using INDEX-MATCH

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

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.

We will utilize this combination to fetch the genre of the movies.

Pull Movie Genre from Details Sheet

The formula will be the following one

=INDEX(Details!$C$4:$C$12,MATCH(B4,Details!$B$4:$B$12,0))

INDEX-MATCH Formula to Pull Values From Another Worksheet

Within the MATCH function, B4 is the lookup_value, and Details!$B$4:$B$12 is the lookup_range. This MATCH portion provides the position and then INDEX pulls the value from Details!$C$4:$C$12 range.

INDEX-MATCH Formula result - Pull Values From Another Worksheet

We have pulled the genre value from the Details worksheet. Write the formula or exercise the AutoFill feature for the rest of the values.

Pull values from Details sheet using INDEX-MATCH

IV. Pull Values Using XLOOKUP

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.

Pull Movie Directors from Details Sheet

The formula will be the following one

=XLOOKUP(B4,Details!$B$4:$B$12,Details!$E$4:$E$12,"Not Found")

XLOOKUP Formula to Pull Values From Another Worksheet

Here B4 is the lookup_value, Details!$B$4:$B$12 is the lookup_range, and Details!$E$4:$E$12 is the range from which we need to pull values. You can notice we have written the sheet name, Details, prior to each of the ranges.

In addition, we have added “Not Found” at the optional field if_not_found.

XLOOKUP Formula result - Pull Values From Another Worksheet

We have pulled the value, director’s name, from another sheet, Details. Do the same for the rest of the values.

Pull values from Details sheet using XLOOKUP

2. Pull Values From Another Worksheet from Another Workbook

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 Pull Values Workbook _Details.xlsx

Pull Values Details Workbook

And our summary (updated summary) table is still in the workbook How to Pull Values From Another Worksheet Excel.xlsx

Pull Values from another worksheet workbook

We will pull the director’s name from the different workbook.

Pull Movie Directors from Details Sheet

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 VLOOKUP. Let’s write the formula.

=VLOOKUP(B4,'[Pull Values Workbook _Details.xlsx]Details'!$B$4:$E$12,4,0)

VLOOKUP to Pull Values from Another Workbook

Here prior to the cell range $B$4:$E$12 we have provided the sheet name (Details) and the workbook name. The workbook name is in the brackets.

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 (‘’).

VLOOKUP Formula result to Pull Values from Another Workbook

We have pulled the value, director’s name, from a worksheet in another worksheet. Do the same for the rest of the values or exercise the AutoFill feature.

Pull values from Another workbook using VLOOKUP

Conclusion

That’s all for the session. We have listed several approaches to pull values from another worksheet in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we might have missed here.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo