6 Ways to Extract Data from Excel Based on Criteria

There may be times when you need to know how to extract data from Excel based on criteria. For example, you may need to use an Excel formula or VBA for this purpose. Today, we’ll show you to extract data based on criteria with various methods and lots of examples in Excel.


📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.


Learn to Extract Data from Excel Based on Criteria in 6 Different Ways

We may need to look for certain data to work with sometimes. But it’s very hard to find what we’re looking for when the dataset is very big. Here we are going to talk about 6 easy methods to extract data based on different criteria in Excel. We are going to extract data based on sales.

Dataset of how to extract data from excel based on criteria

Method 1

1. Array Formula to Get Data Based on Certain Criteria

Here we are going to extract data (sales amount) based on sales. We will use INDEX, IF, and MATCH functions. IF function enables you to compare a value to what you anticipate logically. Within a given table or range, the INDEX function will return either a value or a reference to a value. The MATCH function looks for a given item in a range of cells and returns the relative position of that item within the range. Also, the SMALL function returns the smallest value in a dataset. ROW function gives back the row number of a specific reference. Similarly COLUMN Function gives back the row number of a specific reference.

 ⬇️⬇️ STEPS ⬇️⬇️

  • At first, let’s put our criteria. We assume the start value of sales is $550 and the end value is $1000. The criteria range can be found in column 4. So we put these values in cells I5, I6, and I7.

Putting criteria

  • Then we select cell G12. Then we put the formula below:
=INDEX($B$6:$E$15,SMALL(IF((INDEX($B$6:$E$15,,$I$7)<=$I$6)*(INDEX($B$6:$E$15,,$I$7)>=$I$5),MATCH(ROW($B$5:$E$14),ROW($B$5:$E$14)),””),ROWS(G$11:$G12)),COLUMNS($A$1:A2))

putting formula of how to extract data from excel based on criteria

🔨  Formula Breakdown

👉  (INDEX($B$6:$E$15,,$I$7)<=$I$6)*(INDEX($B$6:$E$15,,$I$7)>=$I$5) indicates that the numbers 1 and 0 represent TRUE and FALSE, respectively (zero). When the arithmetic operation is done in a formula, they are changed.

👉  ROW($B$6:$E$15) calculates the number of rows of the reference.

👉  MATCH(ROW($B$6:$E$15),ROW($B$6:$E$15)) returns relative position of the item.

👉  IF((INDEX($B$6:$E$15,,$I$7)<=$I$6)*(INDEX($B$6:$E$15,,$I$7)>=$I$5),

MATCH(ROW($B$6:$E$15),ROW($B$6:$E$15)),””) returns one specific value if logical test here is TRUE and FALSE otherwise.

👉  SMALL(IF((INDEX($B$6:$E$15,,$I$7)<=$I$6)*(INDEX($B$6:$E$15,,$I$7)>=$I$5),MATCH(ROW($B$6:$E$15),ROW($B$6:$E$15)),””),ROWS(G12:$G$12)) returns the smallest value.

  • Then we press Enter and drag down the formula to cell The final result will be like this.

Using the Array Formula to Get Data Based on Certain Criteria

📕 Read More: Extract Data from Table Based on Multiple Criteria in Excel

Method 2

2. Using Array formula for More Than One Condition

Here we are going to extract data based on sales. We will use INDEX, IF, COUNTIF, and MATCH functions. IF function enables you to compare a value to what you anticipate logically. This formula uses the COUNTIF function to find each name of one Column in another Column. For each piece of data in the first Column, the function will give back a number that shows how many times it found a match. Within a given table or range, the INDEX function will return either a value or a reference to a value. The MATCH function looks for a given item in range of cells and returns the relative position of that item within the range. Also, the SMALL function returns the smallest value in a dataset. ROW function gives back the row number of a specific reference. Similarly, COLUMN Function gives back the row number of a specific reference

 ⬇️⬇️ STEPS ⬇️⬇️

  • At first, let’s put our criteria. We assume the start value of sales is $550 and the end value is $650. So we put these values in cells H6 and H7.

Setting criteria

  • Then we select cell G13. Then we put the formula below:
=INDEX($B$6:$E$15, SMALL(IF(COUNTIF($H$6:$H$7,$E$6:$E$15), MATCH(ROW($B$6:$E$15), ROW($B$6:$E$15)), “”), ROWS(G13:$G$13)), COLUMNS($B$6:B6))

Putting formula of how to extract data from excel based on criteria

🔨  Formula Breakdown

👉  IF(COUNTIF($H$7:$H$7,$E$6:$E$15), MATCH(ROW($B$6:$E$15), ROW($B$6:$E$15)), “”) Here it takes three arguments. Within them, the first of which has to be a logical declaration. If this expression is TRUE, the output is the second argument, and in the case of FALSE, the output is another (argument 3). Step 1 determined this logical expression: TRUE = 1FALSE = 0. (zero).

👉  (INDEX($B$6:$E$15,,$I$7)<=$I$6)*(INDEX($B$6:$E$15,,$I$7)>=$I$5) indicates that the numbers 1 and 0 represent TRUE and FALSE, respectively (zero). When an arithmetic operation is done in a formula, they are changed.

👉  ROW($B$6:$E$15) calculates the number of rows of the reference.

👉  MATCH(ROW($B$6:$E$15),ROW($B$6:$E$15)) returns the relative position of an item.

👉  IF((INDEX($B$6:$E$15,,$I$7)<=$I$6)*(INDEX($B$6:$E$15,,$I$7)>=$I$5),

MATCH(ROW($B$6:$E$15),ROW($B$6:$E$15)),””) returns one specific value if the logical test is TRUE and a different value if the logical test is FALSE.

👉  SMALL(IF((INDEX($B$6:$E$15,,$I$7)<=$I$6)*(INDEX($B$6:$E$15,,$I$7)>=$I$5),MATCH(ROW($B$6:$E$15),ROW($B$6:$E$15)),””),ROWS(G12:$G$12)) returns the smallest value.

  • Then we press Enter and drag down the formula to cell.

  • The final result will be like this.

Using an Array formula for More than One Condition

📕 Read More: Pull Same Cell from Multiple Sheets into Master Column

Method 3

3. Utilizing Filter Feature

Filter Command Feature is a powerful tool for calculating, summarizing, and analyzing data that lets you see comparisons, various patterns, and some trends in the data. Now I’ll demonstrate how to count the number of occurrences that occur each day by using Filter Command Tool.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, we select the headers of our dataset.
  • Then we select Data tab and select Filter.

  • The result will be like this.

  • Now we go to the Sales header and from drop-down box and then Number Filters and then Between…

  • Now, in the next pop-up Custom AutoFilter section, we select 550 from drop-down list that appears when we click on drop-down button that is next to the is greater than or equal to header, and we also choose 600 in the header box that says is less than or equal to. Then we press Ok.

Putting criteria

  • Here is our result.

Inserting Excel's Filter Command Tool to learn how to extract data from excel based on criteria

Method 4

4. Implementing Advanced Filter

Advance Filter Command Tool is a powerful tool for calculating, summarizing, and analyzing data that lets you see comparisons, various patterns, and some trends in the data. Now I’ll demonstrate how to count the number of occurrences that occur each day by using the Advance Filter Command Tool.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we select the entire dataset.
  • Then we select Data tab and select Advanced.

  • After that, range of our chosen data will be shown in a box next to List range
  • Then in a box next to Criteria range, we choose the cells that have the conditions you set. After cell reference numbers that hold predefined conditions, the name of the worksheet will be filled in automatically.
  • Now click OK to finish.

putting criteria to know how to extract data from excel based on criteria

  • Here is our result.

how to extract data from excel based on criteria Implementing the Advanced Filter

Method 5

5. Getting Data from Table

Using Filter option on an Excel worksheet, you can get data from an Excel table. The steps are as below.

⬇️⬇️ STEPS ⬇️⬇️

  • Choosing a cell from the dataset, we press Ctrl and T.
  • In the new box “Create Table”, we put the range. Then press OK in the box.

  • It will look like this.

how to extract data from excel based on criteria Getting data from an Excel Defined Table

  • Now we go to the Sales header and from drop-down box and choose Number Filters and then Between…

  • Now, in the next pop-up Custom AutoFilter box, we select 550 from drop-down list that appears when we click on drop-down button that is next to the is greater than or equal to header, and we also choose 600 in the header box that says is less than or equal to. Then we press OK.

  • Here is our result.

Get data from an Excel Defined Table

Method 6

6. Extracting Data to Another Worksheet

We can extract data from another worksheet. The process is almost the same as using an Advance Filter.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we define criteria as we are looking for sales between $500 and $600.
  • Then in the new worksheet, we select Data tab and select Advanced.

  • After that, we select Copy to another location. Then we put range of our chosen data will be shown in a box next to List range box.
  • Then, in a box next to Criteria range, we choose the cells that have the conditions you set. After cell reference numbers that hold predefined conditions, the name of the worksheet will be filled in automatically.
  • We put the location of the result next to the Copy to box.
  • Now click OK to finish.

  • Here is our result.

how to extract data from excel based on criteria by Extracting Data to Another Worksheet

📕 Read More: 5 Ways to Extract Filtered Data into Another Excel Sheet


How to Extract Specific Number from Cell in Excel

Here we can also extract a specific number from a cell in Excel. We will use several functions here. We will use the INDEX function that will return either a value or a reference to a value. To count the matches alongside two columns, we can only use the SUMPRODUCT function. The SUMPRODUCT function counts and adds up all the ones and zeros in an array. The  ISNUMBER function checks to see if the data in a cell is a number (TRUE) or not (False). ROW function gives back the row number of a specific reference. Similarly, the COLUMN Function gives back the row number of a specific reference

For that we can follow the below steps:

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell C6. Then we put the formula below:
=SUMPRODUCT(MID(0&B6, LARGE(INDEX(ISNUMBER(–MID(B6, ROW(INDIRECT(“1:”&LEN(B6))), 1)) * ROW(INDIRECT(“1:”&LEN(B6))), 0), ROW(INDIRECT(“1:”&LEN(B6))))+1, 1) * 10^ROW(INDIRECT(“1:”&LEN(B6)))/10)

🔨  Formula Breakdown

👉  (INDIRECT(“1:”&LEN(B6))), 0) it searches by the length of the text put in cell B6.

👉   ROW(INDIRECT(“1:”&LEN(B6))), 0 calculates the number of rows of the reference.

👉   (ISNUMBER(–MID(B6, ROW(INDIRECT(“1:”&LEN(B6))), 1)) returns the largest value returned by the INDIRECT function. ISNUMBER converts the array that came by the MID function to TRUE or FALSE.

👉   LARGE(INDEX(ISNUMBER(–MID(B6, ROW(INDIRECT(“1:”&LEN(B6))),1)) Here LARGE Function returns the largest value returned by the INDEX function that will return either a value or a reference to a value.

👉   SUMPRODUCT returns the whole row here.

  • Then we press Enter and drag down the formula.

Drag down formula

  • The final result will be like this.

how to extract data from excel based on criteria by Extracting Specific Number from Cell in Excel

📕 Read More: 4 Ways to Pull Values in Excel from Another Worksheet


📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️  At the side of each Excel worksheet file, there is space where they can practice.


📝  Takeaways from This Article

📌  Readers will be able to know easily how to extract data from excel on criteria with various methods.

📌  They can utilize the Array formula.

📌  They can also use the Array formula with multiple conditions.

📌  Readers can also implement Excel Filter Command.

📌  The readers can again extract data from a different worksheet.

📌  Readers can again extract data from Excel defined table.


Conclusion

That concludes today’s session. These are the methods for using Excel to extract data from excel based on criteria. Hopefully, it was helpful to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen.com, the best Excel solutions provider.

(Visited 86 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo