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.

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.

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.

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.

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

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

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.

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.

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.

The formula will be the following one

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.

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

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.

The formula will be the following one

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.

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

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

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

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

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.

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

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.

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.

The post How to Pull Values From Another Worksheet in Excel appeared first on ExcelDemy.