Sometimes we may need to search for certain data to work with. But when the dataset is huge, it is really hard to find what we are looking for. In this article, we will show you how to **extract data from Excel** based on different criteria.

**Table of Contents**hide

**Download Practice Template**

You can download the free practice Excel template from here.

**5 Ways to Extract Data from Excel Based on Criteria**

This section will discuss 5 different ways to extract data from Excel based on certain criteria.

**1. Implementing Array Formula to Extract Data from Excel Based on Range Criteria**

From the following dataset as an example, we will describe to you the process of extracting data based on range. Suppose, we have a dataset of Studentsâ€™ details, from where we only want to retrieve the student details who got **Marks from 80 to 100**.

The steps to extract data based on a certain range using the** Array** formula are given below.

**Steps:**

- First, store the condition in other cells to work with those later. That means as we will be extracting studentsâ€™ details who got
**Marks from 80 to 100**, we stored**80 as Start Value**and**100 as End Value**in the**Cells I4**and**I5**respectively.

Also, we need to store the column too from where we will look for our stored values. Meaning, the Marks 80 and 100 are in the**Marks column**which is the**3rd column**in our dataset, so we stored**3 as the Column value**in**Cell I6**. - Second, in another cell, where you want the result (we wanted our result in the
**Cell G11**), write the following formula,

`=INDEX($B$5:$E$14,SMALL(IF((INDEX($B$5:$E$14,,$I$6)<=$I$5)*(INDEX($B$5:$E$14,,$I$6)>=$I$4),MATCH(ROW($B$5:$E$14),ROW($B$5:$E$14)),""),ROWS(G11:$G$11)),COLUMNS($A$1:A1))`

- Third, press
**Ctrl + Shift + Enter**on your keyboard.

Then, you will get the first extracted data that match your condition in the result cell. E.g. **Johnny** whose **ID** is **3** got **80 Marks** in **Biology** and his record is stored in the dataset ahead of others, so we got **Johnnyâ€™s ID 3** in the result cell.

- Now, drag around the columns and rows by
**Fill Handle**to retrieve the details of only the students who got**Marks from 80 to 100**.

**Formula Breakdown**

**INDEX($B$5:$E$14,,$I$6)****Output: {60;30;80;55;87;95;100;42;25;18}****Explanation: The INDEX Function**usually returns a single value or an entire column or row from a given cell range.**3**is stored in the**Cell $I$6**, so it returns the entire column no**3**(**Marks**column) from the whole range of the dataset (**$B$5:$E$14**) as output.

**INDEX($B$5:$E$14,,$I$6)<=$I$5**-> becomes,**{60;30;80;55;87;95;100;42;25;18}<=100****Output: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}****Explanation:**We stored**100**in the**Cell $I$5**. As all of the values are less than**100 ($I$5)**, so it returns a column full of**TRUE**.

Similarly,

**INDEX($B$5:$E$14,,$I$6)>=$I$4**-> becomes,- {
**60;30;80;55;87;95;100;42;25;18}>=80** **Output: {FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}****Explanation:**We stored**80**in the**Cell $I$4**. So it returns**TRUE**when the value from the column is**equal or greater than 80**; otherwise, it returns**FALSE**.

- {

**(INDEX($B$5:$E$14,,$I$6)<=$I$5)*(INDEX($B$5:$E$14,,$I$6)>=$I$4)**-> becomes,**{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}*{FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}****Output: {0;0;1;0;1;1;1;0;0;0}****Explanation:**Boolean values have numerical equivalents,**TRUE = 1**and**FALSE = 0 (zero)**. They are converted when performing an arithmetic operation in a formula.

**ROW($B$5:$E$14)****Output: {5;6;7;8;9;10;11;12;13;14}****Explanation:****The ROW function**calculates the row number of a cell reference.

**MATCH(ROW($B$5:$E$14),ROW($B$5:$E$14))**-> becomes,**MATCH({5;6;7;8;9;10;11;12;13;14},{5;6;7;8;9;10;11;12;13;14})****Output: {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}****Explanation:****The MATCH function**returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

**IF((INDEX($B$5:$E$14,,$I$6)<=$I$5)*(INDEX($B$5:$E$14,,$I$6)>=$I$4),MATCH(ROW($B$5:$E$14),ROW($B$5:$E$14)),â€ťâ€ť)**-> becomes,**IF(****{0;0;1;0;1;1;1;0;0;0}),****{1; 2; 3; 4; 5; 6; 7; 8; 9; 10},â€ťâ€ť)****Output: {â€śâ€ť; â€śâ€ť; 3; â€śâ€ť; 5; 6; 7; â€śâ€ť; â€śâ€ť; â€śâ€ť}****Explanation:****The IF function**returns one value if the logical test is**TRUE**and another value if the logical test is**FALSE**.

**SMALL(IF((INDEX($B$5:$E$14,,$I$6)<=$I$5)*(INDEX($B$5:$E$14,,$I$6)>=$I$4),MATCH(ROW($B$5:$E$14),ROW($B$5:$E$14)),â€ťâ€ť),ROWS(G11:$G$11))**-> becomes,**SMALL({â€śâ€ť; â€śâ€ť; 3; â€śâ€ť; 5; 6; 7; â€śâ€ť; â€śâ€ť; â€śâ€ť},ROWS(G11:$G$11))**-> becomes,**SMALL({â€śâ€ť; â€śâ€ť; 3; â€śâ€ť; 5; 6; 7; â€śâ€ť; â€śâ€ť; â€śâ€ť},1)****Output: 3****Explanation:****The SMALL function**returns the**k-th****smallest value**from a group of numbers.**3**is the smallest in this group.

**INDEX($B$5:$E$14,SMALL(IF((INDEX($B$5:$E$14,,$I$6)<=$I$5)*(INDEX($B$5:$E$14,,$I$6)>=$I$4),MATCH(ROW($B$5:$E$14),ROW($B$5:$E$14)),â€ťâ€ť),ROWS(G11:$G$11)),COLUMNS($A$1:A1))**-> becomes,**INDEX($B$5:$E$14,3,,1)****Output: {3; â€śJohnnyâ€ť, 80, â€śBiologyâ€ť}****Explanation:**The**INDEX**function returns a value from a cell range(**$B$5:$E$14**), specified by the value based on a row and column number.

**Read More:** **How to Extract Data from Cell in Excel (5 Methods)**

**2. Implementing Array Formula to Extract Data from Excel Based on Multiple Conditions**

In the above section, we extracted data based on a given range. But in this section, we will show you how to extract data based on multiple conditions.

Look at the same dataset as before but here instead of storing a range of values (Marks 80 to 100) as a condition, we stored multiple conditions such as retrieving studentsâ€™ details from **both Chemistry and Biology departments**.

The steps to extract data based on multiple conditions using the **Array** formula are given below.

**Steps:**

- Firstly, store the conditions in other cells to work with those later. That means as we will be extracting studentsâ€™ details from
**Chemistry**and**Biology**departments, we stored**Chemistry**and**Biology**in the**Cells H5**and**H6**respectively. - Secondly, in another cell, where you want the result (we wanted our result in the
**Cell G11**), write the following formula,

`=INDEX($B$5:$E$14, SMALL(IF(COUNTIF($H$5:$H$6,$E$5:$E$14), MATCH(ROW($B$5:$E$14), ROW($B$5:$E$14)), ""), ROWS(G11:$G$11)), COLUMNS($B$5:B5))`

- Thirdly, press
**Ctrl + Shift + Enter**on your keyboard.

Later, you will get the first extracted data that match your conditions in the result cell. E.g. **Johnny** whose **ID** is **3** is from **Biology Department** and his record is stored in the dataset ahead of others, so we got **Johnnyâ€™s ID 3** in the result cell.

- Now, drag around the columns and rows by
**Fill Handle**to retrieve the details of only the students who are from the**Department**of**Chemistry**and**Biology**.

**Formula Breakdown**

**COUNTIF($H$5:$H$6,$E$5:$E$14)**-> becomes,**COUNTIF({â€śChemistryâ€ť;â€śBiologyâ€ť},{â€śMathâ€ť;â€śPhysicsâ€ť;â€śBiologyâ€ť;â€śChemistryâ€ť;â€śPhysicsâ€ť;â€śPhysicsâ€ť;â€śMathâ€ť;â€śChemistryâ€ť;â€śMathâ€ť;â€śBiologyâ€ť}****Output: {0;0;1;1;0;0;0;1;0;1}****Explanation:****The COUNTIF function**allows to identify cells in the range**$H$5:$H$6**that equals**$E$5:$E$14**.

**IF(COUNTIF($H$5:$H$6,$E$5:$E$14), MATCH(ROW($B$5:$E$14), ROW($B$5:$E$14)), â€śâ€ť)**-> becomes,**IF({0;0;1;1;0;0;0;1;0;1},MATCH(ROW($B$5:$E$14), ROW($B$5:$E$14)), â€śâ€ť)**-> becomes,**IF({0;0;1;1;0;0;0;1;0;1},{****1; 2; 3; 4; 5; 6; 7; 8; 9; 10},â€ťâ€ť)****Output: {â€śâ€ť; â€śâ€ť; 3; 4; â€śâ€ť; â€śâ€ť;â€śâ€ť; 8; â€śâ€ť;10}****Explanation:**The**IF**function has three arguments, the first one must be a logical expression. If the expression evaluates to**TRUE**then one thing happens (argument 2) and if**FALSE**another thing happens (argument 3). The logical expression was calculated in step 1,**TRUE**equals**1**and**FALSE**equals**0 (zero)**. Row no**3, 4, 8**and**10**evaluate**TRUE (1)**.

**SMALL(IF(COUNTIF($H$5:$H$6,$E$5:$E$14), MATCH(ROW($B$5:$E$14), ROW($B$5:$E$14)), â€śâ€ť), ROWS(G11:$G$11))**-> becomes,**SMALL({â€śâ€ť; â€śâ€ť; 3; 4; â€śâ€ť; â€śâ€ť;â€śâ€ť; 8; â€śâ€ť;10},ROWS(G11:$G$11))**-> becomes,**SMALL({â€śâ€ť; â€śâ€ť; 3; 4; â€śâ€ť; â€śâ€ť;â€śâ€ť; 8; â€śâ€ť;10},1)****Output: 3****Explanation:**The**SMALL**function returns the**k-th****smallest value**from a group of numbers.**3**is the smallest in this group.

**INDEX($B$5:$E$14, SMALL(IF(COUNTIF($H$5:$H$6,$E$5:$E$14), MATCH(ROW($B$5:$E$14), ROW($B$5:$E$14)), â€śâ€ť), ROWS(G11:$G$11)), COLUMNS($B$5:B5))**-> becomes,**INDEX($B$5:$E$14, 3, COLUMNS($B$5:B5))**-> becomes,**INDEX($B$5:$E$14, 3, 1)****Output: {3; â€śJohnnyâ€ť, 80, â€śBiologyâ€ť}****Explanation:**The**INDEX**function returns a value from a cell range(**$B$5:$E$14**), specified by the value based on a row and column number.

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

**3. Using Filter Command Tool to Extract Data from Excel Based on Range Criteria**

The Filter command tool in Excel is one of the most used and effective tools to **extract specific data** based on different criteria.

Look at the following dataset. Previously, we used it as our example to extract studentsâ€™ details who got Marks from 80 to 100 by implementing the Array formula. But in this section, we will learn how to do that by utilizing **Excelâ€™s Filter tool**.

The steps to extract data based on a certain range using Excelâ€™s Filter are given below.

**Steps:**

- First, select only the
**header**of the dataset. - Second, go to
**Data -> Filter**.

- Third, it will insert a
**drop-down button**in each header name of the dataset.

- Then, as we want to extract data based on the Marks, so click on the
**drop-down button next to the Marks**column. - Next, from the drop-down list, select
**Number Filters -> Betweenâ€¦**(again, as we are extracting data**between 80 to 100**, so we select the option**Between**. You can select any other options from the list according to your criteria).

- Now, from the pop-up
**Custom AutoFilter**box, select**80**from the**drop-down list**which will appear by simply clicking on the**drop-down button**next to**is greater than or equal to**label, and select**100**in the label box**is less than or equal to**. - Later, click
**OK**.

Finally, you will get all the details only for the students who got **Marks from 80 to 100**.

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

**Similar Readings**

**How to Import Data into Excel from Another Excel File (2 Ways)****VBA Code to Convert Text File to Excel (7 Methods)****How to Convert Text File to Excel Automatically (3 Suitable Ways)****Convert Excel to Text File with Delimiter (2 Easy Approaches)****How to Import Text File with Multiple Delimiters into Excel (3 Methods)**

**4. Utilizing Advanced Filter to Extract Data from Excel Based on Range Criteria**

If you donâ€™t want to go through a lot of steps shown in the Filter section, you can use the **Advanced Filter** option in Excel to extract data based on a given range.

To utilize the advanced filter option in Excel, you have to define the condition in your worksheet to use later. See the following picture where we define our condition of extracting studentsâ€™ details of **Marks 80 to 100** in two different cells as **>=80** and **<=100** under **Marks** and we will be using the **cell reference numbers** of those cells later in our work.

The steps to extract data based on a certain range using Excelâ€™s Advanced Filter are given below.

**Steps:**

- Firstly, select the
**whole data table**. - Secondly, go to
**Data -> Advanced**.

- Finally, 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**. You will see the name of the worksheet will be auto-generated in there, following the cell reference numbers of holding the predefined conditions. - Lastly, click
**OK**.

As a result, you will get all the details only for the students who got **Marks from 80 to 100**.

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

**5. Extract Data from Excel Defined Table Based on Range Criteria**

You can extract data from an **Excel defined table** from your Excel worksheet using the **Filter** option.

Consider the following unorganized dataset, which we will first define as an Excel table and then extract data from there.

The steps to extract data from Excel defined table based on a certain range are given below.

**Steps:**

- In the beginning, select
**any cell**from your dataset and press**Ctrl T**.

- Then, a pop-up
**Create Table**Box will appear, showing the**range of your dataset as values**. Keep the check box**My table has headers**marked. - Later, click
**OK**.

It will **auto-generate a table** based on your dataset with a **drop-down button **along with the headers.

- Then, like the way we previously showed you, click on the
**drop-down button next to the Marks**column as we want to extract data based on the Marks. - Later, from the drop-down list, select
**Number Filters -> Betweenâ€¦**(again, as we are extracting data**between 80 to 100**, we select the option**Between**. You can select any other options from the list according to your criteria).

- Now, from the pop-up
**Custom AutoFilter**box, select**80**from the**drop-down list**which will appear by simply clicking on the**drop-down button**next to**is greater than or equal to**label, and select**100**in the label box**is less than or equal to**. - Lastly, click
**OK**.

In the end, you will get an Excel defined table carrying only the details of studentsâ€™ who got **Marks from 80 to 100**.

**Read More: How to Extract Data From Table Based on Multiple Criteria in Excel**

**Keep in Mind**

- As the range of the data table array to search for the value is fixed, donâ€™t forget to put the
**dollar ($)**sign in front of the cell reference number of the array table. - When working with array values, donâ€™t forget to press
**Ctrl + Shift + Enter**on your keyboard while extracting results. Pressing only**Enter**will work only when you are using**Microsoft 365**. - After pressing
**Ctrl + Shift + Enter**, you will notice that the formula bar enclosed the formula in**curly braces {}**, declaring it as an array formula. Donâ€™t type those**brackets {}**yourself, Excel automatically does this for you.

**Conclusion**

In this article, we have learned how to extract data from Excel based on different criteria. I hope that this article has been very beneficial to you. Feel free to ask any questions if you have regarding the topic.

**You May Also Like To Explore**

**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)****Import Data into Excel from Web (with Quick Steps)****How to Extract Data from a List Using Excel Formula (5 Methods)****Excel VBA: Pull Data Automatically from a Website (2 Methods)****How to Pull Data From Another Sheet Based on Criteria in Excel**