Returning all rows that match criteria in Excel means showing the rows in a dataset that meet specific conditions. For example, this is a dataset showing employee details of a company. We want to return the rows from this dataset based on the **City **name, specifically **New York**.

## Method 1 – Using Excel Formula to Return All Rows That Match Criteria in Excel

The following dataset represents the employee details of a company. Here, the column **Achieved Target** shows the performance of the employees. We want to return the rows based on the value of the **Achieved Target **column.

**Steps: **

- Create a Criteria table like the following one.
- Enter the lower and upper end of the criteria values of the
**Target Achieved**column. Here,**Column**indicates the column number where your criteria is located.

- Create another table with the same headings as the original dataset.

- Input the criteria.

Here, we want to return the rows based on Achieved Target. Now, the Achieved Target column is column number **4** in our dataset. So, in **Column **field, we will insert **4**. Input the lower-end and upper-end criteria value of the **Achieved Target**. Here, we want to return the rows where the **Achieved Target** is between **75-85%**.

** **

- Write this formula in cell
**G11**and press the**Enter**key:

`=IFERROR(INDEX($B$5:$E$16,SMALL(IF((INDEX($B$5:$E$16,,$H$5)<=$H$8)*(INDEX($B$5:$E$16,,$H$5)>=$H$7),MATCH(ROW($B$5:$E$16),ROW($B$5:$E$16)),""),ROWS(G12:$G$12)),COLUMNS($E$11:E11)),"")`

The formula will return the value **Rachel** which is the value in the **Employee Name** column. The Achieved Target of Rachel is 78% and it is between 75-85%, making it the first hit in the table.

**Formula Breakdown**

=IFERROR(INDEX($B$5:$E$16,SMALL(IF((INDEX($B$5:$E$16,,$H$7)<=$H$6)*(INDEX($B$5:$E$16,,$H$7)>=$H$5),MATCH(ROW($B$5:$E$16),ROW($B$5:$E$16)),””),ROWS(G11:$G$11)),**COLUMNS($F$11:F11)**),””)

=IFERROR(INDEX($B$5:$E$16,SMALL(IF((INDEX($B$5:$E$16,,$H$7)<=$H$6)*(INDEX($B$5:$E$16,,$H$7)>=$H$5),MATCH(ROW($B$5:$E$16),ROW($B$5:$E$16)),””),**ROWS(G11:$G$11)**),**1**),””)// COLUMNS($F$11:F11) returns 1 because the count of column is 1 in the given range.

=IFERROR(INDEX($B$5:$E$16,SMALL(IF((INDEX($B$5:$E$16,,$H$7)<=$H$6)*(INDEX($B$5:$E$16,,$H$7)>=$H$5),MATCH(ROW($B$5:$E$16),**ROW($B$5:$E$16)**),””),**1**),1),””) // ROWS(G11:$G$11) returns 1 because the count of rows is 1 in the given range.

=IFERROR(INDEX($B$5:$E$16,SMALL(IF((INDEX($B$5:$E$16,,$H$7)<=$H$6)*(INDEX($B$5:$E$16,,$H$7)>=$H$5),MATCH(**ROW($B$5:$E$16)**,**{5;6;7;8;9;10;11;12;13;14;15;16}**),””),1),1)””) // ROW($B$5:$E$16) returns the array {5;6;7;8;9;10;11;12;13;14;15;16} which are the row numbers.

=IFERROR(INDEX($B$5:$E$16,SMALL(IF((INDEX($B$5:$E$16,,$H$7)<=$H$6)*(INDEX($B$5:$E$16,,$H$7)>=$H$5),**MATCH({5;6;7;8;9;10;11;12;13;14;15;16},{5;6;7;8;9;10;11;12;13;14;15;16})**,””),1),1),””) // ROW($B$5:$E$16) returns the array {5;6;7;8;9;10;11;12;13;14;15;16} which are the row numbers.

** **=IFERROR(INDEX($B$5:$E$16,SMALL(IF((INDEX($B$5:$E$16,,$H$7)<=$H$6)***(INDEX($B$5:$E$16,,$H$7)**>=$H$5),**{1;2;3;4;5;6;7;8;9;10;11;12}**,””),1),1),””)

// MATCH({5;6;7;8;9;10;11;12;13;14;15;16},{5;6;7;8;9;10;11;12;13;14;15;16}) returns {1;2;3;4;5;6;7;8;9;10;11;12} because these are the relative positions.

=IFERROR(INDEX($B$5:$E$16,SMALL(IF((**INDEX($B$5:$E$16,,$H$7)**<=$H$6)*(**{0;0.95;0.78;0.75;0.62;1;0.9;0.85;0.72;0.8;0.82;0.66}**>=$H$5),{1;2;3;4;5;6;7;8;9;10;11;12},””),1),1),””) // (INDEX($B$5:$E$16,,$H$7) returns {0;0.95;0.78;0.75;0.62;1;0.9;0.85;0.72;0.8;0.82;0.66} as these are the reference values it gets.

=IFERROR(INDEX($B$5:$E$16,SMALL(**IF(({0;0.95;0.78;0.75;0.62;1;0.9;0.85;0.72;0.8;0.82;0.66}<=$H$6)*({0;0.95;0.78;0.75;0.62;1;0.9;0.85;0.72;0.8;0.82;0.66}>=$H$5),{1;2;3;4;5;6;7;8;9;10;11;12},””)**,1),1),””) // INDEX($B$5:$E$16,,$H$7) returns the reference array {0;0.95;0.78;0.75;0.62;1;0.9;0.85;0.72;0.8;0.82;0.66}.

=IFERROR(INDEX($B$5:$E$16,**SMALL({“”;2;””;””;””;6;7;8;””;10;11;””},1)**,1),””) // IF(({0;0.95;0.78;0.75;0.62;1;0.9;0.85;0.72;0.8;0.82;0.66}<=$H$6)*({0;0.95;0.78;0.75;0.62;1;0.9;0.85;0.72;0.8;0.82;0.66}>=$H$5),{1;2;3;4;5;6;7;8;9;10;11;12},””) gives {“”;2;””;””;””;6;7;8;””;10;11;””} because it returns the row numbers where the IF conditions match and returns “” if it doesn’t match.

** **=IFERROR(**INDEX($B$5:$E$16,2,1)**,””) // SMALL({“”;2;””;””;””;6;7;8;””;10;11;””},1) returns 2 as the smallest row number. It is to show the values sequentially.

** **=**IFERROR(Matt,””)** // INDEX($B$5:$E$16,2,1) returns the value Matt as it is situated at the intersection of the first column and second row of the B5:E16 range.

=**Matt** // IFERROR(Matt,””) returns Matt as it didn’t find any error. If an error occurs it will return blank.

- Hover over the
**bottom-right**corner of the cell**G12.**The cursor will be changed to the**Fill Handle**feature. - Drag the
**Fill Handle**vertically and horizontally to**AutoFill**the formulas until you get blank cells.

Thus, you will get all the rows where the value of the** Achieved Target **column is between **75-85%**.

Since the table is dynamic, you can enter any values for **Achieved Target** in the criteria table and it will return the rows.

*Note: **In this table, we applied *** conditional formatting **in

*Salary and Achieved Target columns, we have used conditional formatting. The rule is if the cells are non-blank, the Salary column will be in Currency format and the Achieved Target column will be in Percentage format.*

*We don’t know how many rows will return that match the criteria. So, we have applied a new rule in range G12:J24 that if the cells are non-blank border will be added. As a result, the returned rows that match criteria will be bordered automatically*.

## Method 2 – Using Excel’s Filter Button in Excel Table to Return All Rows That Match Criteria

This time, we’ll filter the rows based on the **Salary** column. We want to filter the rows where the salary of the employees is in between **$40,000-$60,000** range.

**Steps:**

- Select the entire dataset.
- Go to the
**Insert**tab and**Tables**group. - Click on the
**Table**option.

- A
**Create Table**dialog box will appear. - Under the
**Where is the data for your table?**field, the selected range will be shown. - As we selected the range including the header, keep the
**My table has headers**option checked. - Press the
**OK**button.

- Excel will convert our dataset into a table.

- Select the
**down arrow**icon at the**bottom-right**corner of the**Salary**header. - Click on the
**Number Filters**option and select the**Between**option.

- A
**Custom Autofilter**dialog box will appear. - Beside the
**is greater than or equal to**field, enter the minimum range which is**40000**. - Beside the
**is less than or equal to**field, enter the maximum range which is**60000.** - Hit the
**OK**button.

- You will get the rows where the value of the
**Salary**column is in the**40,000-60,000**range. You can see a filter icon at the**bottom-right**corner of the column based on which you filtered the rows.

*Note: **The Filter button temporarily modifies the table. You can clear the filters from the Data tab, Sort and Filter group of commands option Clear.*

## Method 3 – Using Excel’s Advanced Filter Feature

This time, we want to return the rows where the** Achieved Target** is between **70-80%**.

**Steps:**

- Create a table to define your criteria. We want to return rows based on the
**Achieved Target**column. So, we created the table by defining the values of**Achieved Target.**It is**>=70%**and**<=80%**.

*Note: **Be careful about the spelling of the headers of the criteria table. The column headers of the criteria table must match exactly with the dataset table. Otherwise, the Advanced Filter won’t detect the column where it will apply the filter.*

- Click on the
**Data**tab and go to the**Sort & Filter**group. - Select the
**Advanced**option right beside the**Filter**command.

- The Advanced Filter dialog box will appear.
- In the
**List range**field, select the range of the entire dataset including the headings. For our dataset, it is**B4:E16**. - In the
**Criteria range**field, select the range of the criteria table including the headings. It’s**G6:H7**for this dataset. - Click on the
**OK**button.

You will get the rows where the **Achieved Targets **of the employees are between **70-80%**.

*Note: **The Advanced Filter option temporarily modifies the table. You can clear the filters from the Data tab => Sort and Filter group of commands =>Clear option.*

## Method 4 – Using FILTER Function to Return All Rows That Match Criteria in Excel

The **FILTER** function is only available in Excel 365.

### Case 4.1 Based on Single Criteria

In our dataset, we want to return the rows based on single criteria. Here our criteria is the **City** column.

*Note**: We have used the Data Validation tool in cell H5. You can choose a specific city from here. If you choose New York, the FILTER function will return the rows where the city name is New York.*

**Steps:**

- Enter this formula in cell
**G9**and press**Enter:**

`=FILTER($B$5:$E$16,($C$5:$C$16=$H$5))`

Thus, you will get all rows where the **City** is **New York**. The **FILTER **function returns the array from **B5:E16 **range if the range **C5:C16** matches with the value of** H5 **cell.

- Select the
**City**in cell**H5**, you will get the rows automatically.

*Note: **If there are not enough empty cells available for the output data of the FILTER function, it’ll give #SPILL!*

### Case 4.2 Based on Multiple Criteria

**Submethod 1 – Using the FILTER Function**

In this dataset, we want to return the rows where the value of the **Achieved Target **is.

**Steps:**

- Write this formula in cell
**G10**and press the**Enter**key.

`=FILTER($B$5:$E$16,($E$5:$E$16>=$H$5)*($E$5:$E$16<=$H$6))`

You will get the rows where the value of the **Achieved Target **column is between the **75-85%** range.

**Formula Breakdown**

=FILTER($B$5:$E$16,($E$5:$E$16>=$H$5)***($E$5:$E$16<=$H$6)**)

=FILTER($B$5:$E$16,**($E$5:$E$16>=$H$5)*****{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}**) // ($E$5:$E$16<=$H$6) this part checks if the range E5:E16 is less than or equal to cell H6. If it matches, it returns TRUE. Otherwise, it returns FALSE.

**=FILTER($B$5:$E$16,{TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}*{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE})** // ($E$5:$E$16>=$H$5) checks if the range E5:E16 is greater than or equal to cell H6. If it matches, it returns TRUE. Otherwise, it returns FALSE.

**{“Matt”,”New York”,50000,0.95;”Michle”,”Seattle”,32000,1;”Steve”,”Los Angeles”,45000,0.9;”Ben”,”Seattle”,56000,0.85;”Jack”,”Los Angeles”,36500,0.8;”Robert”,”New York”,28000,0.82} **// =FILTER($B$5:$E$16,{TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}*{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}) returns the array where both conditions are TRUE.

The rows will return according to your given input in the criteria table.

**Submethod 2 – Combining FILTER and COUNTIF Functions**

Here, we want to get the rows based on two cities. We have applied the** Data Validation** tool in the **G5:G6** range.

- Write this formula in cell
**G10**.

`=FILTER($B$5:$E$16, COUNTIF($G$5:$G$6, $C$5:$C$16))`

- Press the
**Enter**key and you will get the rows where the**City**is**New York**and**Los Angeles**.

**Formula Breakdown**

=FILTER($B$5:$E$16, **COUNTIF($G$5:$G$6, $C$5:$C$16)**)

=**FILTER($B$5:$E$16,** **{1;1;0;1;0;1;0;1;1;1;0;1})**// COUNTIF($G$5:$G$6, $C$5:$C$16) returns {1;1;0;1;0;1;0;1;1;1;0;1} because it returns** 1** if it matches the value with $G$5:$G$6 range. Otherwise, it returns **0**.

**{“Matt”,”New York”,50000,0.95;”Rachel”,”Los Angeles”,34000,0.78;”Bob”,”New York”,64000,0.62;”Steve”,”Los Angeles”,45000,0.9;”Ahmed”,”New York”,48000,0.72;”Jack”,”Los Angeles”,36500,0.8;”Robert”,”New York”,28000,0.82;”Hazard”,”New York”,13000,0.76}// **FILTER($B$5:$E$16, {1;1;0;1;0;1;0;1;1;1;0;1}) returns the array based on the criteria of the COUNTIF function.

Since this formula is dynamic, the returned rows will change according to your given cities.

**Download Practice Workbook**

**<< Go Back to | Excel Match | Learn Excel**