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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for Excel Extract Data from Table Based on Multiple Criteria

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.

Note: This is a basic table to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

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.

Using INDEX-MATCH Array Formula for Excel Extract Data from Table Based on Multiple Criteria

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))

Applying INDEX-MATCH Non Array formula Excel Extract Data from Table Based on Multiple Criteria

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))

Use of INDEX MATCH IF Combinition for Excel Extract Data from Table Based on Multiple Criteria

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))

Use of LOOKUP Function for Excel Extract Data from Table Based on Multiple Criteria

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),"")

Applying INDEX-SMALL Combinition for Excel Extract Data from Table Based on Multiple Criteria

Note: Here we must start our dataset from Row 1 in order to execute this formula.

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),"")

Applying INDEX-AGGREGATE Combinition for Excel Extract Data from Table Based on Multiple Criteria
Note: Likewise the previous method, we must start our dataset from Row 1 in order to execute this formula.

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.

use of Fill Handle tool for Excel Extract Data from Table Based on Multiple Criteria

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

Note: Remember to use CTRL+SHIFT+ENTER to execute the formula.

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)),"")

Using INDEX, MATCH, COUNTIF Functions for Excel Extract Data from Table Based on Multiple Criteria

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.

Use of FILTER Functin for Excel Extract Data from Table Based on Multiple 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.

Using Custom Filter Feature for Excel Extract Data from Table Based on Multiple Criteria

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

Applying Advanced Filter Feature for Excel Extract Data from Table Based on Multiple Criteria

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.

Inserting Table for Excel Extract Data from Table Based on Multiple Criteria

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

Use of SUMPRODUCT Function for Excel Extract Data from Table Based on Multiple Criteria

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))

Applying SUM Function for Excel Extract Data from Table Based on Multiple Criteria

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)

Using SUMIFS Function for Excel Extract Data from Table Based on Multiple Criteria

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.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo