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 table based on multiple criteria in Excel**.

**Practice Workbook**

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

**Extract Data from Table Based on Multiple Criteria.xlsx**

**8 Methods to Extract Data From Table Based on Multiple Criteria**

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 (3 Examples)**

**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 from Excel Based on Criteria (5 Ways)**

**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 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 (6 Effective Methods)**

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

**Read More:** **How to Extract Data from Image into Excel (With Quick Steps)**

**Similar Readings**

**VBA Code to Convert Text File to Excel (7 Methods)****Excel VBA: Pull Data Automatically from a Website (2 Methods)****How to Import Data from Secure Website to Excel (With Quick Steps)****Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP****How to Convert Excel to Text File with Pipe Delimiter (2 Ways)**

**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 tinieast value in a cell range. - 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**.

- 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 (5 Methods)**

**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 gete back an aggregate in a dataset. - The
**IF**fuction does a loical comparison between a given value and expected value. - 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
**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.

**Read More:** **Return Multiple Values in Excel Based on Single Criteria (3 Options)**

**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 (5 Methods)**

**IV. Using FILTER Function**

You can** extract data from 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 to be dragged down. It will show every possible result as soon as we press ENTER.*

**Read More:** **Extract Filtered Data in Excel to Another Sheet (4 Methods)**

### 3. Use Custom Filter Feature

In this method, we will use the **Custom Filter** feature to **extract data from table based on multiple criteria**. Here, we will extract based on** 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 movies 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 are 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** 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**is 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 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.

**Conclusion**

That’s all for today. We have listed several methods to extract data from 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. You can visit our site **Exceldemy** for more releted article.

**Further Readings**

**How to Extract Data from Excel to Word (4 Ways)****Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)****How to Pull Data from Multiple Worksheets in Excel VBA****Transfer Data from One Excel Worksheet to Another Automatically****How to Pull Data From Another Sheet Based on Criteria in Excel****Excel Macro: Extract Data from Multiple Excel Files (4 Methods)****How to Extract Year from Date in Excel (3 Ways)**