2

I've tried looking at answers here and elsewhere but can't really find anything relevant or that works.

What I'm trying to automate is to look at one cell in a column, then look at a range in a different column, and compare if any of the individual cells of that range match any of the content of the cell.

As an example:

Column A Column B Column C
1 Yu-FN, 55 Ov [Formula]
2 Ge_12, 00 Yu
3 Ge-13 FG

In the above example, the Formula in Column C should look at A1 and then check if any cells in B1:B3 match any amount of A1. In this example they do, B2 is contained within A1. A2 & A3 would not match.

Similar to the opposite of a COUNT function. Rather than check when the target is used elsewhere, I need to check if a cell flags certain words. If it does it should report as true, letting me manually review it.

I also need it to be contained to one cell's worth of code so I can use it in Conditional Formatting.

2 Answers 2

0

Conditional Format Rule

# Apply to range
A1:A100        

## Custom formula
=REGEXMATCH(A1, JOIN("|", TOCOL(B:B,1)))

↓ ↓ Important Notes ↓ ↓

  1. The range to which the rule is applied,  A1:A100 in the example above, can be adjusted as needed, but the REGEXMATCH text argument,  A1 in the example, must always refer to the first cell in that range.
  2. If replacing the infinite range reference B:B in the formula with a limited range of rows, the column can be relative, but the row numbers must be absolute.
    • For example, B$1:B$10 is correct, as is $B$1:$B$10, but not ❌B1:B10. If you take that route you may choose to discard TOCOL entirely:
      =REGEXMATCH(A1, JOIN("|", B$1:B$10))
      

Explanation

  1. REGEXMATCH returns TRUE for each row in Column A that matches its regular_expression argument.
  2. The regular_expression is created using a formula:
    • First, TOCOL returns an array of all the values found in Column B. The ignore argument is set to 1 to skip blank cells.
    • JOIN creates the text string for the regular expression from TOCOL's results. The delimiter is | which is the equivalent of the boolean OR in a regular expression.

Google Docs Support Resources

Examples

1. Custom format applied

Table of sample data in range A1:B5 with A1 and A5 highlighted using the Conditional Format rule

2. Calculation broken down

For cell A1 the formula returns TRUE and the format is applied because its value ( "Yu-FN, 55" ) contains the cell B2's value of Yu:

=REGEXMATCH(A1, JOIN("|", TOCOL(B:B,1)))
=REGEXMATCH(A1, JOIN("|", {"OV"; "Yu"; "FG"; "Ls"}))
=REGEXMATCH("Yu-FN,  55", "OV | Yu | FG | Ls")
=TRUE

3. Rule settings

Part of the formula is hidden in the image of the GUI.
Conditional format rule dialog box populated with custom formula and range set to A1:A100

2
  • 1
    That's very helpful! Thank you. I knew there had to be a simpler way! Also, since I messed it up, you may want to edit your answer to include $absolute $reference on the ranges. When I put it in without that it messed up. Wouldn't want anyone new to make the same mistake!
    – ZweiHydra
    Jan 4 at 21:32
  • 1
    Oh I see! Very helpful @blindspots, thank you for clarifying. I spent nearly 6 total hours looking for an answer like this before, so I'm just trying to make sure others get the most helpful answer possible. You've been a great help!
    – ZweiHydra
    Jan 5 at 0:21
2

You can achieve this in Google Sheets using an array formula combined with the SEARCH function. The SEARCH function is case-insensitive and can be used to find a substring within a string. To check if any cell in the range B1:B3 is contained within the cell A1, you can use the following formula in cell C1:

=ARRAYFORMULA(SUM(IFERROR(SEARCH(B$1:B$3,A1))))

This formula works by searching for each value in the range B1:B3 within A1. If a match is found, SEARCH will return the position of the match; otherwise, it will result in an error. IFERROR converts these errors to zeros.

To use this formula directly in conditional formatting, you can follow these steps:

  1. Select the cell or range where you want to apply the formatting (e.g., A1).

  2. Go to Format → Conditional formatting in the Google Sheets menu.

  3. In the Conditional format rules pane on the right, under the "Format cells if..." section, choose "Custom formula is".

  4. Enter the formula in the field provided: =ARRAYFORMULA(SUM(IFERROR(SEARCH(B$1:B$3,A1))))
    Please note that we use absolute references ($B$1:$B$3) for the range to keep it constant when the conditional formatting rule is applied to multiple cells.

  5. Choose the formatting style you want to apply when the condition is true.

  6. Click "Done" to apply the rule.

With this rule in place, any cell in column A that contains any substring from cells B1 to B3 will be highlighted with the formatting style you chose.

Another Alternative 2: use =SUMPRODUCT(IFERROR(SEARCH(B$1:B$3, A1)))

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.