Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel ROW Function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel MMULT Function
  • How to use Excel LOOKUP Function
  • How to get address of named range in Excel

Data Analysis Examples

  • Number and Text Filters Examples in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • Create Scatter Chart in Excel
  • How To Filter Data in Excel
  • Example of COUNTIFS with variable table column in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in next 30 days
  • Excel Data validation must contain specific text
  • Excel Data validation allow uppercase only

How to check cell that contains one of many with exclusions in Excel

by

To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions.

Formula

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0)
*(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)

Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation.

Worked Example:   Sum 2d range with multiple criteria in Excel

Explanation

In the example shown the formula in C5 is:

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0)
*(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)

where “include” is the named range E5:E9, and “exclude” is the named range G5:G6.

How this formula works

At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range “include”.

Worked Example:   Highlight rows that contain in Excel

In the right SUMPRODUCT, SEARCH looks for all strings in the named range “exclude”.

In both parts of the formula, SEARCH returns numeric positions when strings are found, and errors when not. The ISNUMBER functions converts the numbers to TRUE and errors to FALSE, and the double negative converts the TRUE FALSE values to 1 and 0.

Worked Example:   Count dates by day of week in Excel

The result at this point looks like this:

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

Then:

=(1>0)*(0=0)
=TRUE*FALSE
=1

 

Post navigation

Previous Post:

How to check if cell contains number in Excel

Next Post:

How to check if cell contains some words but not others in Excel

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
  • Remove text by position in a cell in Excel
  • FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation
  • How to remove trailing slash from url in Excel
  • Split numbers from units of measure in Excel
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • Sum through n months in Excel
  • Get days before a date in Excel
  • Get last weekday in month in Excel
  • Extract date from a date and time in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
  • PV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles