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

Lookup and Reference Examples

  • How to get first row number in range in Excel
  • How to get address of named range in Excel
  • Count rows with at least n matching values
  • Two-way lookup with VLOOKUP in Excel
  • Basic INDEX MATCH approximate in Excel

Data Analysis Examples

  • Get column index in Excel Table
  • Understanding Pivot Tables in Excel
  • Chart Axes in Excel
  • How to perform a t-Test in Excel
  • Reverse List in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • How To Create Drop-down List in Excel
  • Excel Data validation date in next 30 days
  • Excel Data validation allow uppercase only
  • Excel Data validation don’t exceed total

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:

{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 extract last two words from text string 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 cells equal to case sensitive 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
  • How to count specific words in a cell in Excel
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • Split dimensions into two parts in Excel Worksheet
  • How to add a line break with a formula while joining text strings in Excel
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Basic Overtime Calculation Formula in Excel
  • Add months to date in Excel
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • DB function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles