digestVidSign in
summary

excelisfun - Excel Magic Trick 1383: Conditional Format Row w OR Logical Test with Multiple Partial Text Criteria

Thumbnail of the video “excelisfun - Excel Magic Trick 1383: Conditional Format Row w OR Logical Test with Multiple Partial Text Criteria”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Science Technology Education
Tags: science, technology, education
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

Short Summary

Main recommendation: To conditionally format an entire row when a text cell contains any of several partial text criteria (an OR logical test), build a custom formula in New Rule using a SEARCH-based array, and use a simpler LOOKUP-based formula as a faster alternative. Apply the formula to the whole data set and convert the range to an Excel table so the formatting expands as new records are added.

Key Points

  • Excel has no built-in conditional format for "contains any of these items," so you must use "Use a formula to determine which cells to format" with a custom formula that returns TRUE or FALSE for every cell.
  • Each cell in the row needs its own logical test, so every formula references the job title cell (for example F8) with the column locked, letting the formatting copy across and down.
  • The first method uses SEARCH with multiple find_text items, ISNUMBER to convert positions into TRUE/FALSE, and OR to return one TRUE if any match exists.
  • The second method uses LOOKUP with approximate match and a very large lookup value (2^15), which always returns the last numeric position, and is faster to calculate.
  • Converting the data to an Excel table (Control+T, named "ContainsCriteria") makes the conditional formatting range expand automatically when you type new records below.

Details Worth Noting

  • Concrete instructions:
    • Set up a helper area off to the side, and in the top-left cell enter =SEARCH(criteria_range_items, F8). Press F4 three times to lock the F column (not the row) so the formula can copy across and down.
    • Press F9 to check the array of positions and errors; any numeric position means the row should be formatted.
    • Wrap it as =OR(ISNUMBER(SEARCH(items, F8))). Because the criteria are an array, enter it with Control+Shift+Enter in the cell (the curly brackets confirm array evaluation), then copy it over and down to verify the TRUE/FALSE pattern.
    • Copy the top-left formula in edit mode (F2, Control+C, Escape), highlight the whole data set (first row, Control+Shift+Down Arrow), press Control+Backspace to return to the active cell, then open Conditional Formatting, New Rule, "Use a formula," paste the formula, choose a format, and click OK.
    • Faster alternative: =LOOKUP(2^15, SEARCH(items, F8)). Lock the column with F4, copy down, and paste the formula into the same New Rule dialog. No Control+Shift+Enter is needed.
    • Create the table: click a single cell in the data, press Control+T, and name it in Table Tools > Design > Properties (the speaker uses "ContainsCriteria").
  • The speaker notes that only five functions (LOOKUP, INDEX, AGGREGATE, SUMPRODUCT, and CHISQ.TEST) can perform array operations without Control+Shift+Enter.
  • The Conditional Formatting dialog treats any nonzero number as TRUE and errors as FALSE, which is why the LOOKUP version works without ISNUMBER or OR.
  • Caution: the formula ranges in the helper example are fixed (J8 to J14), so adding rows only extends the formatting if the data is an Excel table.

Bottom Line

Conditional formatting for multiple partial text matches is achievable with a custom SEARCH-based formula, and the LOOKUP version is simpler and quicker, provided the data is in an Excel table so new records are included automatically.