Filtering data helps focusing on certain aspects of a data set. Excel has built-in features for this, an Excel formula for filtering data was not in the software’s scope until the introduction of dynamic array functions. In this guide, we’re going to show you how you can use Excel formula for filtering data.

## Syntax

## Steps

- Select the range of cells that will be populated with filtered values
- Start the formula with
**=****IFERROR(**function to return empty string when an error occurs) - Continue with
**INDEX(** - Select or type in the range reference that contains your original list
**B:B,** - Continue with the
**SMALL(**function which provides the row indexes of cells - Next, use
**IF(**to return an array that contains the numbers and empty strings - Use an equation to filter
**$E$3=C:C,**the criteria cell should be an absolute reference - Continue with
**,ROW(C:C),””),**which will provide*TRUE/FALSE*conditions for the IF function - Type in
**ROW()-ROW(E5)**to generate an incremental number for array from the IF function - Type in
**)),””)**and press**Ctrl + Shift + Enter**to complete the*array*formula

## How

To filter out values from a range, we need to pinpoint the cells that meet a certain condition, and retrieve them from the original list. Keep mind that we’re going to need to create an array formula to avoid to creating several helper columns, and use a single Excel formula for filtering data. As a result, the formulas will return array values.

Our example returns values from column B by searching the value of cell E3 in column C. This condition leads to the logical test,

This test returns an array of Boolean values (TRUE and FALSE). For example, if the value of cell E3 is “I”, the logical test returns an array like below,

*{FALSE;FALSE;TRUE;TRUE;TRUE;…….;FALSE}*

These Boolean values become the logical test values for the **IF** function. The **IF** function provides the row numbers of cells that meet the criteria, and returns empty strings for others.

The **IF** function here returns an array. This time an array of row numbers and empty strings.

*{“”;””;3;4;5;…….;””}*

The next step is sorting the row numbers in our new array. The **SMALL** function can return the *n*th smallest number from an array. Also note that Excel evaluates string values as almost infinitely large numbers, making any other number small in comparison. This is the reason why the **SMALL** function is used instead of the **LARGE** function. To assign an ‘n’ value to the **SMALL** function, we use the **ROW** function again with a single cell that should be one cell above the first row to return numbers from 1, and use a relative reference to increase its row number. As a result; SMALL(IF($E$3=C:C,ROW(C:C),””),ROW()-ROW(E5)) formula returns a row index value which will be used by the **INDEX** function to return a value from a non-empty cell.

*{1}*

The **INDEX** function selects the reference to return values, and the **IFERROR** envelops the nested formula to avoid errors.

Finally, press the Ctrl + Shift + Enter combination instead of just pressing the Enter key to enter your formula as an array formula, and you’re done!