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

Lookup and Reference Examples

  • Lookup entire row in Excel
  • Approximate match with multiple criteria in Excel
  • Perform case-sensitive Lookup in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • Get column index in Excel Table
  • How to Sort by Color in Excel
  • How To Compare Two Lists in Excel
  • Create Scatter Chart in Excel
  • Add Outline to Data in Excel

Data Validation Examples

  • Excel Data validation whole percentage only
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells
  • How To Create Drop-down List in Excel
  • Excel Data validation date in specific year

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:   Count cells equal to case sensitive 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:   Sum if equal to either x or y 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:   How to count specific words in a range 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
  • UNICHAR function: Description, Usage, Syntax, Examples and Explanation
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • REPLACE, REPLACEB functions: Description, Usage, Syntax, Examples and Explanation
  • Join cells with comma in Excel
  • Two ways to Compare Text in Excel
  • List holidays between two dates in Excel
  • Display Date is same month in Excel
  • Count times in a specific range in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Get day from date in Excel
  • Tax rate calculation with fixed base in Excel
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • SLN function: Description, Usage, Syntax, Examples and Explanation
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles