Method 1 – Use the Advanced Filter to Pull Data from Another Sheet
We have a dataset of customers and their payment history. We are going to pull out the details of the customers who paid via card.
Steps:
- In the second spreadsheet, go to the Data option from the ribbon.
- Select Advanced from the Sort & Filter group of commands.
- Select Copy to another location.
- Select the List range from the source sheet.
- Click on the Criteria range and put data based on the criteria we want.
- Select the cell where you want to copy the extracted data and press OK.
- You can see the extracted data and use it later.
Read More: How to Get Data from Another Sheet Based on Cell Value in Excel
Method 2 – Use the VLOOKUP Formula in Excel to Get Data From Another Sheet
Here is a dataset of the customers.
We are going to input the missing data from another spreadsheet Sheet2.
Steps:
- Select Cell E5 and insert the following:
=VLOOKUP(C5,Sheet2!B5:C8,2,0)
- Hit Enter.
- Drag down the formula through the column.
Read More: Extract Filtered Data in Excel to Another Sheet
Method 3 – Combine INDEX and MATCH Functions to Obtain Data from Another Sheet
We have a customer dataset with their payment information.
In another sheet Sheet3, we are going to pull out the Amount values of the customers.
Steps:
- Select Cell D5 and insert the following:
=INDEX('INDEX & MATCH Functions'!B5:E5,MATCH($B$5,'INDEX & MATCH Functions'!$B$4:$E$4,0))The MATCH Function finds the exact match of a value from the array of another sheet. The INDEX Function returns that value from the list.
- Press Enter and drag down the cursor to see the rest of the results.
Method 4 – Use the HLOOKUP Function to Pull Data from Another Sheet Based on Criteria in Excel
We have a spreadsheet of customer payment histories.
We are going to put the data into another spreadsheet Sheet4. We have a helper column there.
Steps:
- Select the Cell E5.
- Use the formula:
=HLOOKUP($B$5,'HLOOKUP Function'!$B$4:$E$8,Sheet4!D5+1,0)
- Hit Enter and drag down the cursor to the cells below for the result.
Download the Practice Workbook
Related Articles
- Pull Same Cell from Multiple Sheets into Master Column in Excel
- How to Pull Data from Multiple Worksheets in Excel
- Extract Data from One Sheet to Another Using VBA in Excel
- How to Pull Data from Multiple Worksheets in Excel VBA
- Excel Macro: Extract Data from Multiple Excel Files
<< Go Back To Extract Data Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi,I really appreciate these page. Very professional explaination of excel.
Please can you help me show me what excel formula to use in one of my project.
If I have one column consists only of English words and the other column consists only of another language words which corresponds to the English words, what excel formula to use so that if I typed one of the English words, it will output the corresponding word in the other language?
Looking forward to hearing from you,
Appreciate your help and advice.
Regards,
Sam.
Hi SAM,
Glad to hear that you liked my article.
I think your project is based on the basic application of VLOOKUP. Please check this article (Method 1)-
https://www.exceldemy.com/compare-multiple-columns-in-excel-using-vlookup/#1_Compare_Multiple_Columns_in_Excel_and_Return_Matching_Data_Using_VLOOKUP
I hope it’ll be helpful for you.
Hi Nuraida: Thank you for your great explanations.
I am using one data set on sheet one for all of my data for a SUMIFS project.
However I need to input my answers in a table on Sheet 2.
How do I transfer the SumIfs answers to sheet 2.
I copied the answer table and put it on Sheet 1 but I know this is not correct.
Thank you.
Hello CHARLOTTE,
Glad to hear that you liked my article.
Please check this article (Method 7)-
Use SUMIFS Function to Sum Between a Date Range from Another Sheet
I hope you will find a solution.
i want a formula to move entire row to another sheet if “status” column reads “Closed”
there are columns A to U.
And rows 3 to 320.
(i have case data sheet in which there are active case and closed cases, once case turn closed it should move to another sheet)
please help me.
Hi SHANON,
Please try to apply below VBA Code after selecting the whole dataset:
Sub move_rows()
For Each myCell In Selection.Columns(2).Cells
If myCell.Value = “Closed” Then
myCell.EntireRow.Copy Worksheets(“Sheet2”).Range(“A” & Rows.Count).End(3)(2)
End If
Next
End Sub
You may replace the column number in line 2 according to the placement of your “status” column. Also the sheet name (Sheet2) in line 4 to your required sheet.
Hi,
Great article, I need help though!
I have 3 tabs of data, that I want to reference into one sheet with the unfiltered data for the group.
How can I make the depot specific sheets match into the unfiltered one?
Hi Bob,
Please check this one. It might be helpful-
https://www.exceldemy.com/excel-reference-cell-in-another-sheet-based-on-cell-value/
Good Afternoon from Ghana, West Africa.
Please I have one Excel Sheet with School Fees Collection of the entire school which I Locked. I followed the Advance Filter Method and it’s abled to Pull the Data Set of Each students Names Fees Status Payment for Each Class Class on different Sheets.
However, it does not update the different Class List of Payment Status when the Main Data Sheet is changed; New Name added or New Payment entered.
Please, how can I use the ADVANCE FILTER method to be pulling the Data AUTOMATICALLY?
Or Any other Method that I can use for Automatic Pull of the Data?
Thank You
Hi Muniru Tahiru,
Thank you for sharing your problem. To solve this you can go through this article. I hope it will help you.
https://www.exceldemy.com/automatically-update-one-excel-sheet-from-another-sheet/#top_ankor
Please let us know if you have other queries.
Thanks!