Filter values in array formula

Excel allows us to filter values in an array with the IF, ISNUMBER and MATCH functions. This step by step tutorial will assist all levels of Excel users in filtering in or out some specific values in a data set.  

Figure 1. Final result: Filter values in array

Final formula: =COUNT(IF(ISNUMBER(MATCH(B3:D5,G2:G3,0)),B3:D5))

Syntax of the IF function

IF function evaluates a given logical test and returns a TRUE or a FALSE.  

Syntax

=IF(logical_test, [value_if_true], [value_if_false])

  • The arguments “value_if_true” and “value_if_false” are optional.  If left blank, the function will return TRUE if the logical test is met, and FALSE if otherwise.  

Syntax of the ISNUMBER function

The ISNUMBER function tests whether a value is a number and returns TRUE; FALSE if otherwise

Syntax

=ISNUMBER(value)

  • Value – any value that we want to test if a number or not

Syntax of the MATCH function

MATCH function is used to search for a specific value in a range of cells.  It returns the #N/A error value if the search is unsuccessful.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

The parameters are:

  • lookup_value – a value which we want to find in the lookup_array
  • lookup_array – the range of cells containing the value we want to match
  • [match_type] optional; the type of match; if omitted, the default value is 1; We use 0 to find an exact match

Setting up Our Data

Our data shows a 3×3 table with values 0, 1, 2 and 3.  In cells G2 and G3, we specify the values we want to filter, which are numbers 0 and 3.    

Figure 2. Sample data to filter values in array

Filter Values in Array

We want to filter the values 0 and 3 in our data.  

Figure 3. Entering the formula to filter values in array

To filter values in an array, we follow these steps:  

Step 1.  Select cell G5.

Step 2.  Enter the formula: =COUNT(IF(ISNUMBER(MATCH(B3:D5,G2:G3,0)),B3:D5))

Step 3.  Press Ctrl + Shift + ENTER because this is an array formula.  

First, the formula matches our data with the filter values 0 and 3 through the MATCH function “MATCH(B3:D5,G2:G3,0)”. The ISNUMBER evaluates the matched values and returns TRUE if there is a match. Otherwise it returns FALSE.

The result is an array with these values:  {TRUE,TRUE,FALSE;TRUE,FALSE,FALSE;FALSE,FALSE,TRUE}

Figure 4. Resulting array after the matched values are evaluated if a number or not

The COUNT and IF functions return the count for the filtered values. As a final result, the value in cell G5 is 4, which is the count of all values 0 and 3 in our data.  

Exclude Filters in Array

There are instances when we want to exclude the filtered values from our data.  

Figure 5. Entering the formula to exclude filtered values in array

To exclude filtered values in an array, we follow these steps:  

Step 1.  Select cell G5.

Step 2.  Enter the formula: =COUNT(IF(1-ISNUMBER(MATCH(B3:D5,G2:G3,0)),B3:D5))

Step 3.  Press Ctrl + Shift + ENTER .

The result in cell G5 is the reverse of the result in our previous example. Out of the 9 data points, we excluded the filtered values 0 and 3, and came up with 5 values.  

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

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

avatar