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.
- 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.
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.
We are going to input the missing data from another spreadsheet ‘Sheet2’.
- Select Cell E5.
- Type the formula:
➧ 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.
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.
Here on another sheet ‘Sheet3’, we are going to pull out the Amount values of the customers.
- 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))
- 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.
We are going to pull out the data into another spreadsheet ‘Sheet4’. We can see a helping column which needs for the calculations.
- Select the Cell E5.
- Write down the formula:
- Hit Enter and drag down the cursor to the cells below for the result.
Download Practice Workbook
Download the following workbook and exercise.
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.