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 OFFSET function
  • Get nth match with INDEX / MATCH in Excel
  • How to retrieve first match between two ranges in Excel
  • How to get address of last cell in range in Excel
  • How to use Excel FORMULATEXT function

Data Analysis Examples

  • How To Perform and Interpret Regression Analysis in Excel
  • Excel Line Chart
  • How to create running total in an Excel Table
  • Add Outline to Data in Excel
  • Everything about Charts in Excel

Data Validation Examples

  • Excel Data validation specific characters only
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in next 30 days
  • Excel Data validation don’t exceed total
  • Excel Data validation whole percentage only

Highlight cells that contain one of many in Excel

by

This tutorial shows how to Highlight cells that contain one of many in Excel using the example below;

Formula

=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0

Explanation

To highlight cells that contain one of many text strings, you can use a formula based on the functions ISNUMBER and SEARCH, together with the SUMPRODUCT function. In the example shown, the conditional formatting applied to B4:B11 is based on this formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0

How this formula works

Working from the inside out, this part of the formula searches each cell in B4:B11 for all values in the named range “things”:

--ISNUMBER(SEARCH(things,B4)

The SEARCH function returns the position of the value if found, and and the #VALUE error if not found. For B4, the results come back in an array like this:

Worked Example:   Sum matching columns in Excel
{8;#VALUE!;#VALUE!}

The ISNUMBER function changes all results to TRUE or FALSE:

{TRUE;FALSE;FALSE}

The double negative in front of ISNUMBER forces TRUE/FALSE to 1/0:

{1;0;0}

The SUMPRODUCT function then adds up the results, which is tested against zero:

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

Any non-zero result means at least one value was found, so the formula returns TRUE, triggering the rule.

Worked Example:   How To Compare Two Lists in Excel

Ignore empty things

To ignore empty cells in the named range “things”, you can try a modified formula like this:

=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things<>"",things),B4)))>0

This works as long as the text values you are testing don’t contain the string “FALSE”. If they do, you can extend the IF function to include a value if false known not to occur in the text (i.e. “zzzz”, “####”, etc.)

Worked Example:   Count multiple criteria with NOT logic in Excel

Case-sensitive option

SEARCH is not case-sensitive. If you need to check case as well, just replace SEARCH with FIND like so:

=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>

Post navigation

Previous Post:

Highlight cells that contain in Excel

Next Post:

Highlight cells that end with 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
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Text Functions References and Examples
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • Extract most frequently occurring text in Excel
  • How to get last word in a cell in Excel
  • How to join date and text together in Excel
  • Display Date is workday in Excel
  • Convert Excel time to decimal minutes
  • Get day name from date in Excel
  • Get project end date in Excel
  • Bond valuation example in Excel
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • Calculate original loan amount in Excel
  • VDB function: Description, Usage, Syntax, Examples and Explanation
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles