How to Return All Rows That Match Criteria in Excel

In this article, we’ll be showing different methods to return all rows that match the criteria in Excel. It is a very important feature considering its application in data sorting or indexing based on specific conditions. We have used some array formulas, functions, and Excel-provided features to return all rows that match the criteria.

For this tutorial, we will use this dataset containing employee details for XYZ Company. Here we want to find out employee information based on different criteria, such as the Cities they are from, their Salary range, the Target Completed, etc., like in the image below.

Download Practice Workbook

You can download the practice workbook from here.

How to Return All Rows That Match Criteria in Excel

1. Return All Rows Based on Range Criteria

In this method, we will use the range criteria to return rows in Excel. We may have conditions like the following:

This condition implies that we need to find employee details for those employees who have completed their monthly target above 80%. For such scenarios, follow this method.

📌 Steps:

• First, create a new table heading like the original table, like in the image below. In this new table, we will keep the returned rows.
• Then select the first cell of the new table data area. In our case, we are selecting G11. Copy this formula into the formula bar and press Enter.

• Again, select cell G6 and drag the Fill Handle horizontally.
• Further, drag the Fill Handle vertically. Drag it until it shows #NUM! or a number error. This means that there is no match left. You can clear this number error row if you want since it’s not necessary to show it.

2. Return All Rows for Specific Criteria Using Excel Filter Button

The Excel Filter Button is one of the most efficient ways to return all rows that match the criteria. The method is simple, diverse, and easy to use with even a large dataset. This time we want to find out if employees have salaries between $40,000-$60,000. The steps are below.

📌 Steps:

• First, select the entire dataset. In our case, it’s B4:E16.

• Second, go to the Insert tab in the Ribbon, and in the Tables section, select Table.

• A small selection box will appear named Create Table. Third, in this box, check the ‘My table has headers’ option. Click on OK.

• As a result, the dataset will be converted into a table with a filter button in each of the headers.

• Then click on the filter button for the column you want to apply the criteria to. In our case, we will click on the Salary column’s filter button. Click on Number Filters. And select any criteria to apply. We will select ‘Between’ according to our criteria.

• After that, a dialog box will appear called Custom Autofilter. In this dialog box, give the criteria values. In the first box, write 40000 as it says, ‘is greater or equal to’. And in the second one, give 60000 as value, as it says ‘is less than or equal to’.

• Now clicking on OK will change the table so that it only contains the data that maintains those criteria.

The other data is still there, but it is hidden due to the filter. They will appear once you clear the filter.

3. Return All Rows for Specific Criteria Using Excel’s Advanced Filter Feature

Advanced Filter is a very handy tool when it comes to applying custom filters. In this method, we will focus on this particular feature of Excel for returning all rows that match the criteria. This time we have the condition that we need to find employees whose target has been completed between 70%–80%. The steps are below.

📌 Steps:

• First, select all the data. In this case, we will select B4:E16.
• Second, go to the Data tab in the Ribbon and select Advanced Filter under Sort and Filter

• As a result, the Advanced Filter selection box will appear. In this box, select the criteria range as shown in the image below.

• Now pressing OK will give us our final result.

4. Return All Rows Using the FILTER Function (Excel 365 Users Only)

This method will work only on the Excel 365 version. The FILTER function is not available for other versions of Excel yet. This method is quite simple and easy to use. We will have the same condition as with the first method. The steps are below.

📌 Steps:

• First, create a new table heading for the returned rows like in the first method.
• Now in the table data area, select the first table data cell, insert this formula in the formula bar, and press Enter.

5. Return All Rows From a Range Based on Multiple Criteria (Excel 365 Only)

We will use multiple criteria in this method. We are still using the FILTER function, but this time the condition is to find employees from New York and Los Angeles only. The steps are below.

📌 Steps:

• Initially, create a separate table heading for the returned rows like the previous methods.
• Then select the first cell in the table data range (in our case, it is G6). Insert the following formula in the formula bar and press Enter.

6. Return All Rows That Match Specific Values (Excel 365 Only)

We can use the FILTER function to return all the rows for a specific condition only. For example, we want the names of all the employees from New York City only. The steps are exactly the same as in the previous methods.

The difference will be in the formula. In this case, the formula will be:

How to Return the Number of Rows That Match Criteria in Excel

For this specific task, we will first use a condition to isolate the rows. Then we will use the TEXTJOIN function to return the row values. For example, we want the row numbers where the employee is from ‘Seattle’.

To do that, first, select the cell where you want the row numbers. Then, in the cell, write this formula and press Enter. This will give you the row numbers.

Things To Remember

• The criteria should exactly be as we showed them in the third method. Otherwise, the Advanced Filter will not work.
• The Filter Button and Advanced Filter feature temporarily modify the table. You can clear the filters from the Data tab > Sort and Filter section > Clear.
• The Target column in the first method implies the column to be compared or searched.
• If there are not enough empty cells available for the output data of the FILTER function, it’ll give #SPILL!

Frequently Asked Questions

Q1. How do I return all rows where a certain date falls within a specified range in Excel?

Answer: For this kind of scenario, when the criteria are within a range and the data type is a date, you can go for the first or the second method. But the second method will be more applicable in this case since the Excel filter button calculation will be more precise and accurate for date type values.

Q2. Is there a way to return rows based on a specific range of numeric values?

Answer: Yes, the method for this kind of criteria is shown in the third method. There, you can set your own numeric range using the Advanced Filter feature and return rows based on those criteria.

Q3. What formula can I use to filter and display rows based on both single and multiple criteria in Excel?

Answer: The FILTER function based formulas offer this feature for both single and multiple criteria. Obviously, some more functions and modifications will be needed based on the given criteria.

Conclusion

Those are the six most common methods to return all rows that match the criteria in Excel. The last three methods are exclusive to Excel 365. But the first three methods are applicable to all versions. If you’re still having trouble with any of these examples, let us know in the comments. Our team is ready to answer all of your questions. For any Excel-related problems, you can visit our website, Exceldemy, for solutions.