How to Extract Data From Table Based on Multiple Criteria in Excel

Extracting the data at the time of necessity is the prime factor of using any spreadsheet, and Excel is not an exception. You store data in tabular form in Excel to extract them. Today we will go through 8 easy and effective methods to extract data from a table based on multiple criteria in Excel.

How to Extract Data From Table Based on Multiple Criteria in Excel: 8 Methods 

First things first, let’s get to know about the dataset which is the base of our examples. Here, in the following picture, you can see the Movie, Genre, Actor, and Release columns. This is our dataset.

In the following picture, you can see the dataset with the criteria.

Here, for example, we will provide the Genre and Actor name as criteria, and based on these criteria we will extract the Movie name. Afterward, we will go through 8 easy methods to extract data based on multiple criteria. Here, we used Excel 365. You can use any available Excel version.

1. Extracting Single Data Based on Multiple Criteria

In this section, we will extract a single value based on multiple criteria. Based on the criteria only one value will be fetched. Let’s explore.

I. Using INDEX-MATCH Array Formula

Here, we will use the combination of the INDEX and MATCH functions to extract data based on multiple criteria. You can see the criteria, where we put Action as Genre and Christian Bale as Actor. Next, we will extract the Movie name using the INDEX-MATCH array formula.

Steps:

• First of all, we will type the following formula in cell H7.

• Since it is an array formula, press CTRL+SHIFT+ENTER to execute it if you do not have EXCEL 365.
• However, if you are using EXCEL 365, press ENTER.
• Therefore, you can see the result in cell H7.

Change the criteria values, and you will find updated values.

Read More: How to Extract Specific Data from a Cell in Excel 

II. Use of INDEX-MATCH Non-Array Formula

In this method, we will form a non-array formula combining INDEX and MATCH functions.

Steps:

• In the first place, we will type the following formula in cell H7.

• Afterward, press ENTER.
• Hence, you can see the result in cell H7.

Feel free to modify the criteria values, you will find updated values.

Read More: How to Extract Data Based on Criteria from Excel

III. Applying  INDEX-MATCH-IF Combination

In earlier sections, we have checked the conditions and multiplied them to force them to act together. We can leave the multiplication by using the IF function.

Steps:

• In the beginning, we will type the following formula in cell H7.

• Since it is an array formula, press CTRL+SHIFT+ENTER to execute it if you do not have EXCEL 365.
• However, if you are using EXCEL 365, press ENTER.
• Therefore, you can see the result in cell H7.

Read More: How to Extract Data from Excel Sheet 

IV. Use of LOOKUP Function

We can use the LOOKUP function to extract data based on multiple criteria. The LOOKUP function performs a matching lookup in a range and returns the corresponding value.

Steps:

• In the beginning, we will type the following formula in cell H7.

No need to press CTRL + SHIFT + ENTER to execute.

• Then, press ENTER.
• As a result, you can see the result in cell H7.

Change the criteria value to see whether the formula is working perfectly or not for other values.

2. Extracting Multiple Data Based on Multiple Criteria

In this section, we will extract multiple values based on multiple criteria. Based on the criteria, several values will be fetched. Let’s explore.

I. Using INDEX-SMALL Combination

we can use numerous function combinations for extracting multiple data based on multiple criteria. Here, we will use the INDEX – SMALL functions. In addition to these functions, we will use the IF, ROW, and IFERROR functions.

Steps:

• In the first place, we will type the following formula in cell H4.

• After that, press ENTER.
• Therefore, you can see the result in cell H4.
• Moreover, we will drag down the formula with the Fill Handle tool.

• Hence, you can see the multiple outcomes for multiple criteria in cells H4, H5, and H6.

Read More: How to Extract Data from a List Using Excel Formula

II. Applying INDEX-AGGREGATE Combination

The AGGREGATE function in Excel allows us to perform various tasks. One function for several operations. We can use this function to return multiple values based on multiple criteria. Along with that, we will need a few helper functions INDEX, IF, ROW and IFERROR.

Steps:

In the beginning, we will type the following formula in cell H4.

• Afterward, press ENTER.
• Therefore, you can see the result in cell H4.
• Moreover, we will drag down the formula with the Fill Handle tool.

• As a result, you can see the multiple outcomes for multiple criteria in cells H4, H5, and H6.

III. Applying Combination of INDEX-MATCH-COUNTIF Functions

In this metod, we will use the INDEX, MATCH, and COUNTIF functions., along with some other functions to extract data based on multiple criteria.

Steps:

• Firstly, we will type the following formula in cell H7.

• Afterward, press ENTER.
• Therefore, you can see the result in cell H7.
• Moreover, we will drag down the formula with the Fill Handle tool.

• Hence, you can see the multiple outcomes for multiple criteria in cells H7, H8, and H9.

Read More: How to Extract Data from Cell in Excel 

IV. Using FILTER Function

You can extract data from a table based on multiple criteria quickly using the FILTER function. However, this function is only available in Excel 365 and higher versions of Excel.

Steps:

• First, we will type the following formula in cell H7.

Here, The FILTER function strains an array of data based on given criteria.

• Afterward, press ENTER.
• Therefore, you can see the multiple outcomes for multiple criteria in cells H7, H8, and H9.
• This formula need not be dragged down. It will show every possible result as soon as we press ENTER.
  

3. Use Custom Filter Feature

In this method, we will use the Custom Filter feature to extract data from a table based on multiple criteria. Here, we will extract based on the Release year of the movies. We will extract the movies that are between the years 2006 and 2013.

 Steps:

• First, we will select only the header of the dataset.
• Second, go to the Data tab >> select Filter.

• As a result, you can see a drop-down button in each header name of the dataset.

• Then, as we want to extract data based on the Release year, we will click on the drop-down button next to the Release column.
• Next, from the drop-down list, select Number Filters >> select Between.

• At this point, a Custom AutoFilter dialog box will pop up.
• Then, we will type 2006 in the greater than or equal to box.
• After that, we will type 2013 in the less than or equal to box.
• Later, click OK.

• Finally, you will get all the details only for the movie based on Release year 2006 and 2013.

4. Apply Advanced Filter Feature

If you don’t want to go through a lot of steps shown in the previous method, you can use the Advanced Filter option in Excel to extract data from Table based on multiple criteria.

Here, to utilize the Advanced Filter option in Excel, you have to define the condition in your worksheet to use later. In the following picture, we define our condition of extracting movie details of Release year in cells G5 and H5. Here, we type >=2007 in cell G5 and we type <=2013 in cell H5. Later, we will be using the cell reference numbers of those cells later in our work.

Steps:

• First of all, we will select the whole data table.
• After that, from the Data tab >> select Advanced.

• At this point, an Advanced Filter dialog box will appear.
• After that,  you will see the range of your selected data in the box next to the List range option.
• Then, in the box next to the Criteria range, select the cells carrying the defined conditions.
• Here, we selected cells G4:H5 in the Criteria range box.
• Lastly, click OK.

• As a result, you will get all the details only for the movies that were released between 2008 to 2013.

5. Insert Table to Extract Data from Table Based on Criteria

You can extract the data based on criteria in Excel by using a Table. Here, first, we will insert a Table, and after that, we will use the Custom Filter feature to extract data.  Here, we will pull based on the Release year of the movies. We will extract the movies that are between the years 2008 and 2013.

Steps:

• First of all, to insert a Table, we will select the dataset by selecting cells B4:E20.
• After that, we will go to the Insert tab >> select Table.

• At this point, a Create Table dialog box will appear.
• Then, make sure My table has headers marked.
• Then, click OK.

• Therefore, you can see the Table.
• Here, you can easily notice that the headers of the Table has drop-down icon.

• Then, as we want to extract data based on the Release year, we will click on the drop-down button next to the Release column.
• Next, from the drop-down list, select Number Filters >> select Between.

• At this point, a Custom AutoFilter dialog box will pop up.
• Then, we will type 2008 in the greater than or equal to box.
• After that, we will type 2013 in the less than or equal to box.
• Later, click OK.

• As a result, you will get all the details only for the movies that are released between 2008 to 2013.

6. Apply Excel SUMPRODUCT Function to Abstract Data from Table

In this method, we will use the SUMPRODUCT function to extract data from table based on multiple criteria in Excel.

Here, for this method, we have modified the dataset. The dataset now includes the Name, Subject, and Score columns. Along with that, you can see the criteria where we will find the Score in cell G6 for the name Rose, and for the subject Math.

Steps:

• In the beginning, we will type the following formula in cell G6.

• At this point, press ENTER.
• Therefore, you can see the result in cell G6.

7. Utilize SUM Function to Extract Data from Table Based on Multiple Criteria

In this method, we will use the SUM function to extract data from table based on multiple criteria.

Steps:

• In the beginning, we will type the following formula in cell G6.

• After that, press ENTER.
• Hence, you can see the result in cell G6.

8. Use SUMIF Function to Extricate Data from Excel Table

In this method, we will use the SUMIFS function to extract data from table based on multiple criteria in Excel.

Steps:

• In the beginning, we will type the following formula in cell G6.

• In addition, press ENTER.
• As a result, you can see the result in cell G6.

Practice Section

You can download the above Excel file to practice the explained method.

Practice Workbook

You are welcome to download the practice workbook from the following link.

Extract Data from Table Based on Multiple Criteria.xlsx

Conclusion

That’s all for today. We have listed several methods to extract data from the table based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other approaches that we have missed here.

Further Readings

• Excel Formula to Get First 3 Characters from a Cell
• How to Extract Month and Day from Date in Excel
• How to Extract Month from Date in Excel
• How to Extract Year from Date in Excel

<< Go Back To Extract Data Excel | Learn Excel