Lamisa Musharrat
You can copy and paste values from one worksheet to another manually, but this process is very time-consuming when you are dealing with a huge amount of dataset. So, to make your work easier we would like to discuss here the possible ways to pull values from another worksheet in Excel using functions.
📁 Download Excel File
Download the Excel file we used to create this article so you can practice.
Learn to Pull Values in Excel from Another Worksheet with These 4 Suitable Approaches
There are some processes that can make this process of copying data from different worksheet handier. Suppose we have one worksheet, containing book names, and their genre.
You want to extract values in this sheet from other worksheets where there is details information regarding this, including their publication date, movie release date, and author names.
So let’s begin, and follow these four examples to learn more. In this section, we will learn to pull values only within a worksheet.
1. Utilizing INDEX-MATCH Function
In this approach, we are going to add one section with our previous two sections, and for this data we are going to extract values from the details worksheet in Excel. We are going to use the INDEX function and the MATCH function for this purpose. Follow the steps below to know how to do this.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select a cell where you want to assign the copied data. We have used cell D6 and we are going to insert the book-published data here.
- After that, insert the following formula.
🔨 Formula Breakdown
INDEX(Details!$E$6:$E$11,MATCH(B6,Details!$B$6:$B$11,0))
👉 Here, in MATCH(B6,Details!$B$6:$B$11,0) , cell B6 represent the lookup_value and, Details!$B$6:$B$11 is for looup_range. Therefore, this MATCH function provides the position of the data.
👉 INDEX function pull value from the given Details!$E$6:$E$11 range.
- Then, after pressing Enter from your keyboard, you will see that the value is automatically copied from your preferred worksheet.
- After that, you have to fill in the column. If you want to copy the entire column just use Fill Handle icon.
- Drag it downwards and you will see that all data are copied.
📕 Read More: Pull Same Cell from Multiple Sheets into Master Column
2. Using VLOOKUP Function
You can also use the VLOOKUP function to lookup at your desired data in other worksheets. For example, we are going to pull the data of the book writer in our summary sheet. So, go through the following steps to know how to do that.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, specify a cell where you want to see the copied data. We have chosen cell D6.
- Then you have to type the formula below using VLOOKUP function.
=VLOOKUP(B6,Details!$B$6:$E$11,3,0)
🔨 Formula Breakdown
VLOOKUP(B6,Details!$B$6:$E$11,3,0)
👉 Here, B6 represents the lookup_value and Details!$C$6:$C$11 is known as lookup_array, 3 is the col_number.
👉 Also, notice that Details! is the preferred sheet name followed by a ! sign.
- After that, just Enter the following formula and you will see the value you wanted from another worksheet.
- But your work is not completed yet. If you want to copy a full column from a different sheet, you have to use Fill Handle icon and pull it downwards.
- You will find that all values of that column are copied from the other worksheet.
3. Applying XLOOKUP Function
Another function of Excel that may be uncommon to you is the XLOOKUP function. The difference between this function with the VLOOKUP function is that it defaults to the exact match whereas the VLOOKUP search for the closest match.
Follow the steps below to know how to use it for this purpose.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select a cell where you want to assign the copied data. We have used cell D6 and we are going to insert the book-published data here.
- After that, insert following formula.
🔨 Formula Breakdown
XLOOKUP(B6,Details!$B$6:$B$11,Details!$C$6:$C$11,”Not Found”)
👉 Here, B6 represents lookup_value and at the same time Details!$C$6:$C$11 is known as lookup_range, Details!$B$6:$B$11 is range from where this function will pull values. And if the data we are looking for is not found there it will show the message “Not Found”.
👉 Also, notice that, Details! is the sheet name followed by a ! sign.
- Thirdly, press Enter from your keyboard to see the pulled value in that cell.
- Finally, as you have pulled one value using the formula given, you can fill the other cell by the Fill Handle icon.
- Just drag this icon downwards to the end of the column and see the copied values in other cells.
4. Direct Pull with Cell Reference
Also, you can pull values from other worksheets of Excel only by inserting the cell reference that you want to pull.
This is a simple way and we are going to show the steps of it below.
⬇️⬇️ STEPS ⬇️⬇️
- Suppose we want to pull the data of the writer of the book. Therefore, firstly, specify a cell where you want to see the copied data. We have chosen the cell
- Then you have to type the formula below using the VLOOKUP function.
- Here, cell D6 is used as we have extracted the data of the writers from the D6 cell of the Details worksheet.
- After that, just Enter the following formula and you will see the value you wanted from another worksheet.
- But your work is not completed yet. If you want to copy a full column from another sheet, you have to use the Fill Handle icon and pull it downwards.
- You will find that all values of that column are copied from the other worksheet.
How to Extract Values in Excel from Another Workbook
You can also pull data from another worksheet even if this worksheet is in another workbook. Here we are going to discuss how to extract values from another workbook. For this example, we will use the previous workbook details sheet to pull value from that.
We are now using another workbook shown below.
Follow the steps below to learn more.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, open a fresh workbook and specify a cell where you want to assign the pulled dataset. Here we have opened a new workbook named “Pull Values from Another Workbook”.
- After that, we specified that cell E6 pulls the data from the previous workbook named “Pull Values from Another Worksheet”.
- Secondly, in that cell insert the formula below.
🔨 Formula Breakdown
VLOOKUP(B6,'[Pull Values from Another Worksheet.xlsx]Summary’!$B$6:$E$11,2,0)
👉 Here, B6 represents the lookup_value.
👉 Before the range of the cells $B$6:$E$11, the name of the workbook is given in the [ ] bracket, which is [Pull Values from Another Worksheet.xlsx].
👉 Also, notice that Summary! is the sheet name . As we want to estimate workbook and a worksheet in it simultaneously we have used this (‘ ‘) sign. So, we have written, ‘[Pull Values from Another Worksheet.xlsx]Summary’!.
👉 Finally, 2 is the col_number in the sheet from where we will pull the value.
- Thirdly, press Enter from your keyboard to see the pulled value in that cell.
- Finally, as you have pulled one value using that formula given, you can fill the other cell with Autofill Feature of excel. We have used the Fill handle icon, to fill other cells.
- Just drag this icon downwards to the end of the column and see the copied values in other cells.
📕 Read More: 5 Ways to Extract Filtered Data into Another Excel Sheet
📝 Takeaways from This Article
📌 You have learned to pull values from another worksheet in Excel within the same workbook, using functions.
📌 When the workbook is different you can still pull data from that following these methods.
Conclusion
Last but not the least, copying formulas from one worksheet to another may be a tedious process when you use the copy-and-paste feature, for large data. Instead, try these examples and you will be doing things easily in much less time. We have shown the possible approach to pull values from another worksheet in Excel. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our Excelden page to enhance your Excel-related knowledge.
Related Articles
- 6 Ways to Extract Data from Excel Based on Criteria
- Extract Data from Table Based on Multiple Criteria in Excel
