Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Create Frequency Distribution in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How To Compare Two Lists in Excel
  • How to sum a total in multiple Excel tables
  • How To Remove Duplicates In Excel Column Or Row?

References

  • Count rows that contain specific values in Excel
  • Count rows with at least n matching values
  • How to use Excel OFFSET function
  • How to get last row in text data in Excel
  • Approximate match with multiple criteria in Excel

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation number multiple 100
  • Excel Data validation must not contain
  • Excel Data validation only dates between
  • Excel Data validation specific characters only

Check if range contains a value not in another range in Excel

by

To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA.

The MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, so it will return an array of results, one per element in the lookup array.

Worked Example:   Weighted average in Excel

See example below:

Formula

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

Explanation

In the example shown, the formula in F6 is:

=SUMPRODUCT(--(ISNA(MATCH(lista,listb,0))))>0

How the formula works

MATCH is configured for “exact match”. If a match isn’t found, MATCH will return the #N/A error. After match runs, it returns have something like this:

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

We take advantage of this by using the ISNA function to test for any #N/A errors.

Worked Example:   Count cells that contain numbers in Excel

After ISNA, we have:

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

We use the double negative (double unary) operator to convert TRUE FALSE values to ones and zeros, which gives us this:

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

SUMPRODUCT then sums the elements in the array, and the result is compared to zero for force a TRUE or FALSE result.

Worked Example:   How To Skip Blanks Using Paste in Excel

 

Post navigation

Previous Post:

How to generate random date between two dates in Excel

Next Post:

Popularly Used Excel Functions and their examples

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel FALSE Function
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • XOR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get first day of previous month in Excel
  • How to calculate Quarter of Date in Excel
  • Custom weekday abbreviation in Excel
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Generate series of dates by weekends in Excel

Grouping

  • Running count group by n size in Excel
  • How to randomly assign data to groups in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • Group times into unequal buckets in Excel

General

  • Sum by group in Excel
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • Using Existing Templates in Excel
  • How to increase by percentage in Excel
  • Count cells that do not contain many strings in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning