< Go Back

Range contains a value not in another range

Check if Range Contains a Value Not in another Range

Generic Formula

=SUMPRODUCT (--(ISNA(MATCH(rngA,rngB,0))))>0

Explanation

Excel uses a formula to find if a range contains, one or more values, are not in the other defined range. For this purpose, you can use SUMPRODUCT along with MATCH and ISNA.

Example

For your reference, please check the image. The formula in G6 is:

=SUMPRODUCT(--(ISNA(MATCH(alist,blist,0))))>0

Figure 1. Example to check if range contains a value that not in another range

How This Formula Works

Usually, the MATCH function searches for a single value and returns the same if found. While in the current example, we have given an array to the MATCH function so we’ll receive an array of results. We have configured the MATCH function to find the exact match, so, in case, if it is not found, we’ll receive the #N/A error from the MATCH function. We’ll observe the following results after the MATCH runs.

=SUMPRODUCT(--(ISNA({3;5;6;2;#N/A;4})))>0

You can clearly see “#NA” error in the above product. Thus, to inspect #N/A errors, we have used ISNA function. Though, the error may not occur always.

ISNA gives the following results:

=SUMPRODUCT(--({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}))>

Further, the conversion of TRUE FALSE values to ones and zeros is done with the help of double negative operator (double unary), which results in the following:

=SUMPRODUCT({0;0;0;0;1;0})>0

In the end, the elements are summed by the SUMPRODUCT in the array, and we receive a TRUE or FALSE result after it is compared to zero. This is how this formula works to check if range contains values, not in another range.

Note

This formula is a combination of three different functions, to give you the perfect result, when you are in need of sorting the excel files with a high amount of data.

 

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