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.

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

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

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

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

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

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

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