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.
=INDEX($B$5:$B$20,MATCH(1,($H$5=$C$5:$C$20)*($H$6=$D$5:$D$20),0))
Formula Breakdown
- MATCH(1,($H$5=$C$5:$C$20)*($H$6=$D$5:$D$20),0) → the MATCH function locates the position of a lookup value in a range.
- Output: 4
- INDEX($B$5:$B$20,MATCH(1,($H$5=$C$5:$C$20)*($H$6=$D$5:$D$20),0)) →the INDEX function returns the value at a given location in a range
- Output: The Dark Knight
- Explanation: The Dark Knight is the Movie based on the Genre and Actor.
- 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.
=INDEX($B$5:$B$20,MATCH(1,INDEX(($H$5=$C$5:$C$20)*($H$6=$D$5:$D$20),0,1),0))
Formula Breakdown
- The MATCH function locates the position of a lookup value in a range.
- The INDEX function returns the value at a given location in a range
- Output: Logan
- Explanation: Logan is the Movie based on the Genre and Actor.
- 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.
=INDEX($B$5:$B$20,MATCH(1,IF($C$5:$C$20=$H$5,IF($D$5:$D$20=$H$6,1)),0))
Formula Breakdown
- The MATCH function locates the position of a lookup value in a range.
- The INDEX function returns the value at a given location in a range
- The IF function makes a logical comparison between a value and the value we expect.
- Output: The Dark Knight
- Explanation: The Dark Knight is the Movie based on the Genre and Actor.
- 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.
=LOOKUP(1,1/($C$5:$C$20=$H$5)/($D$5:$D$20=$H$6),($B$5:$B$20))
Formula Breakdown
- LOOKUP(1,1/($C$5:$C$20=$H$5)/($D$5:$D$20=$H$6),($B$5:$B$20)) → the LOOKUP function is used to search in a single row and single column.
- Output: Inception
- Explanation: Inception is the Movie based on the Genre and Actor.
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.
=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")
Formula Breakdown
- The INDEX function returns the value at a given location in a range
- The ROW function gets the row number of defined cells.
- The SMALL function output the K-th tiniest value in a cell range.
- The IFERROR function returns a black cell if there is any error in the formula.
- Output: Logan.
- Explanation: Logan is the Movie based on the Genre and Actor.
- 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.
=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")
Formula Breakdown
- The INDEX function returns the value at a given location in a range
- The ROW function gets the row number of defined cells.
- The AGGREGATE function gets back an aggregate in a dataset.
- The IF function does a logical comparison between a given value and an expected value.
- The IFERROR function returns a black cell if there is any error in the formula.
- Output: Logan.
- Explanation: Logan is the Movie based on the Genre and Actor.
- 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.
=IFERROR(INDEX($B$5:$B$20,MATCH(0,COUNTIF(H6:$H$6,$B$5:$B$20)+IF($C$5:$C$20<>$H$5,1,0)+IF($D$5:$D$20<>$H$6,1,0),0)),"")
Formula Breakdown
- The INDEX function returns the value at a given location in a range.
- The COUNTIF function counts cells in a range that meets a single condition.
- The IFERROR function returns black cell if there is any error in the formula.
- Output: Logan.
- Explanation: Logan is the Movie based on the Genre and Actor.
- 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.
=FILTER(B5:B20,(H5=C5:C20)*(H6=D5:D20))
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.
=SUMPRODUCT((B5:B10=G4)*(C5:C10=G5)*(D5:D10))
Formula Breakdown
- SUMPRODUCT((B5:B10=G4)*(C5:C10=G5)*(D5:D10)) → the SUMPRODUCT function gives the result of the sum of the values corresponding to an array.
- Output: 70
- Explanation: Here, 70 is the score for Rose in Math.
- 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.
=SUM((B5:B10=G4)*(C5:C10=G5)*(D5:D10))
Formula Breakdown
- SUM((B5:B10=G4)*(C5:C10=G5)*(D5:D10)) → the SUM function adds the values in a range of cells.
- Output: 70
- Explanation: Here, 70 is the score for Rose in Math.
- 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.
=SUMIFS(D5:D10,B5:B10,G4,C5:C10,G5)
Formula Breakdown
- SUMIFS(D5:D10,B5:B10,G4,C5:C10,G5) → the SUMIFS function sums up the values that meet the given criteria.
- Output: 70
- Explanation: Here, 70 is the score for Rose in Math.
- 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.