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

Data Analysis

  • How to Use Solver Tool in Excel
  • Get column index in Excel Table
  • Understanding Pivot Tables in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Excel Frequency Function Example

References

  • How to get relative column numbers in a range in Excel
  • How to retrieve first match between two ranges in Excel
  • How to get first row number in range in Excel
  • Extract data with helper column in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation specific characters only
  • Excel Data validation whole percentage only
  • Data validation must not exist in list
  • Excel Data validation no punctuation

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.

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.

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.

 

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

  • IF, AND, OR and NOT Functions Examples in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • IF with wildcards in Excel
  • Complete List of Excel Logical Functions, References and Examples

Date Time

  • How to calculate nth day of week in month in Excel
  • How to calculate project start date based on end date in Excel
  • Extract time from a date and time in Excel
  • How to get same date next year or previous year in Excel
  • Get days before a date in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Map inputs to arbitrary values in Excel
  • Group arbitrary text values in Excel
  • How to randomly assign people to groups in Excel
  • Group times into unequal buckets in Excel

General

  • How to Delete Cells, Row and Rows in Excel
  • Check if range contains a value not in another range in Excel
  • Creating and Opening an existing file in Excel
  • Excel Ribbon Quick Overview For Beginners
  • Advanced Number Formats in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning