How to Pull Data From Another Sheet Based on Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel can easily pull data from another sheet based on different criteria by using different functions. We don’t need to type data again and again for different sheets. Today we are going to know about another use of these functions of Excel.


Pull Data From Another Sheet Based on Criteria in Excel: 4 Ways

1. Use of Advanced Filter to Pull Data From Another Sheet

Advanced Filter is one of the most common and easiest ways to pull data from another sheet based on criteria. Let’s consider, we have a dataset of the customer and their payment history. In the next spreadsheet, we are going to pull out the details of the customers who paid through Card.

Use of Advanced Filter to Pull Data From Another Sheet

STEPS:

  • In the second spreadsheet, go to the Data option from the ribbon.
  • Select Advanced from the Sort & Filter group of commands.

  • Now in the dialogue box, select ‘Copy to another location.
  • Select the List range from the source sheet.

  • Then click on the Criteria range and put data based on the criteria we want.

  • After that, select the cell where we want to copy the extracted data and press OK.

  • Finally, we can see the extracted data and use them for further purposes.

Use of Advanced Filter to Pull Data From Another Sheet

Read More: How to Get Data from Another Sheet Based on Cell Value in Excel


2. Use of VLOOKUP Formula in Excel to Get Data From Another Sheet

VLOOKUP means Verticle Lookup. To search for certain data in a column, we use the VLOOKUP Function. Here is a dataset of the customers.

Use of VLOOKUP Formula in Excel to Get Data From Another Sheet

We are going to input the missing data from another spreadsheet ‘Sheet2’.

STEPS:

  • Select Cell E5.
  • Type the formula:
=VLOOKUP(C5,Sheet2!B5:C8,2,0)

➧ NOTE: Here first of all we put the lookup value that we wanted to search in the next sheet. Then select the sheet range from the next sheet. Also, input the column number in which we want to pull out the data. Finally, for the exact match, we write 0.

  • Now hit Enter.
  • After that drag down the formula through the column.
  • At last, we can see the result.

Read More: Extract Filtered Data in Excel to Another Sheet


3. Combine INDEX & MATCH Functions to Obtain Data From Another

INDEX & MATCH Functions combo is a popular and powerful tool in Microsoft Excel to return the value from a particular portion of the list. Using this combo, we can pull data from another sheet based on criteria. Assuming we have a customer dataset with their payment information.

Combine INDEX & MATCH Functions to Obtain Data From Another

Here on another sheet ‘Sheet3’, we are going to pull out the Amount values of the customers.

STEPS:

  • At first, select Cell D5.
  • Then type the formula:
=INDEX('INDEX & MATCH Functions'!B5:E5,MATCH($B$5,'INDEX & MATCH Functions'!$B$4:$E$4,0))

➧ NOTE: Here 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 result.
  • Finally, it’s done.


4. Use of HLOOKUP Function to Pull Data From Another Sheet Based on Criteria in Excel 

The HLOOKUP Function does the horizontal lookup to bring back the value from the data. Let’s say we have a spreadsheet of customers’ payment history.

Use of HLOOKUP Function to Pull Data From Another Sheet

We are going to pull out the data into another spreadsheet ‘Sheet4’. We can see a helping column which needs for the calculations.

STEPS:

  • Select the Cell E5.
  • Write down 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.

Use of HLOOKUP Function to Pull Data From Another Sheet Based on Criteria in Excel


Download Practice Workbook

Download the following workbook and exercise.


Conclusion

By following these ways, we can easily get data from another sheet based on criteria in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Articles

<< Go Back To Extract Data Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

10 Comments
  1. 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.

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

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

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo