# Filter row based on matching substrings in cell values of another row

I have some data from which I'd like to return only cells that contain specific text.

In Row 1 I have a sequence of numbers, from 1 to 5, and in the row below each number a text string.

I want to FILTER Row 1 based on searching for text in the cells in Row 2.

1. The search values consist of individual letter characters.
2. Each cell I'm checking for the search values is made up of multiple text strings separated by commas. For example, `b,dec10` or `c,b,dec10`
3. A match is made if any one of the search values is found in the cell, but it must be a perfect match. For example,
• `b,dec10` and `c,b,dec10` are both matches for `b` because `b` is a comma separated string in each of them.
• `c,b,dec10` is a match for `c` for the same reason.
• `b,dec10` is NOT a match for `c` because `c` does not exist as a complete comma delimited string.
• `r,nov12` and `b,dec10` are matches for `r` and `b` because they each contain at least one of the lookup values.

### Example Search

A B C D E
1 1 2 3 4 5
2 b,dec10 x,nov12 c,nov10 c,b,dec10 r,nov12

Using the sample data above, a search for `b` or `x` should return `{1,2,4}`

I have tried the following formula:

``````=IFERROR(
FILTER(A1:E1,
(A2:E2="b")+(A2:E2="x"))
``````

It returns values if cells in `A2:E2` contain `b` or `x` and nothing else, but doesn't return anything if there is anything else in the cell.

How should I modify my formula?

• Is this in Google Sheets? Be sure to add the relevant tag. Dec 16, 2023 at 23:19
• I edited your question for clarity. If I misinterpreted anything please edit and correct. Dec 18, 2023 at 0:05

This is a good candidate for combining FILTER and REGEXMATCH

# Formulae

### 1.  Match `b`

``````=FILTER(A1:E1,
REGEXMATCH(A2:E2, "(^|,)b(,|\$)"))
``````

Matches `b` if it is both:

1. `(^|,)` - At the start of the string `^` or `|` preceded by a comma `,`
2. `(,|\$)` - followed by a comma `,` or `|` at the end of the string `\$`

### 2.  Match `b` or `x`

``````=FILTER(A1:E1,
REGEXMATCH(A2:E2, "(^|,)b|x(,|\$)"))
``````
• same rules as before except checks for either `b` or `x`

### 3.  Match Cell Value

``````=FILTER(A1:E1,
REGEXMATCH(A2:E2, "(^|,)"&G2&"(,|\$)"))
``````
• `G2` can contain a single value `b` or multiple values `b|x`

### 4.  Match Values From a Range

``````=FILTER(A1:E1,
REGEXMATCH(A2:E2,
"(^|,)"&JOIN("|",G2:G3)&"(,|\$)"))
``````
• Uses JOIN to concatenate a range of values with an or `|` operator as delimiter.

• @GeorgiSlaveev I presume you are aware that the answer above supports `bb` or `cc` or any other string you want to use. I only used `b` in the example based on your question. Dec 20, 2023 at 3:59