< Go Back

Filter values in array formula

Filter values in an array formula

There is sometimes a need to filter out a certain amount of data from huge data. Excel makes of the MATCH, IF and ISNUMBER built-in functions to filter out the data contained in an array.

Generic formula

{=COUNT(IF(ISNUMBER(MATCH(data, filter, 0)),data))}

How does the formula work?

  1. The innermost function used is the MATCH function whose generic parameter ‘data’ are all the values of a named range of cells and these are checked against the values contained in the generic parameter ‘filter’.
  2. The ISNUMBER function encloses the MATCH function where this function evaluates the matched values for a logical TRUE or FALSE values.
  3. The IF function encloses the two functions as specified above which is used to exclude all the filtered values.
  4. The COUNT function only verifies the result.


  1. Let us consider an array set containing different numeric values as shown below.

                        Figure 1. The list of random array values and filter value entered

Let us use the generic formula as shown to exclude some of the values.

Figure 2. The generic formula used to exclude the filter values.

The output obtained is as shown below when used the formula.

Figure 3. The result obtained when used the generic formula

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment