Excel function for filtering a list of substrings to include & exclude

Question

Excel function where if any row values in column A include a keyword from the list in column D but does not include a keyword from the list in column E, write True, otherwise, write nothing/keep it blank.

Attempts:

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($D$2:$D$3, A2)))) * (ISERR(SEARCH($E$2:$E$3, A2)))) > 0, "True", "")

This does not work but if the Exclude is singular and not a list, it does:

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($D$2:$D$3, A2)))) * (ISERR(SEARCH($E$2, A2)))) > 0, "True", "")

Also tried:

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($D$2:$D$3, A2)))) * (ISERR(SEARCH({"FFCD, "VVC"}, A2)))) > 0, "True", "")

But curly brackets don't seem to do anything and I prefer to have the keywords in a list for easier changeability.

Copilot:

=IF(AND(ISNUMBER(SEARCH(N2:N18, B2)), NOT(ISNUMBER(SEARCH(O2, B2)))), "True", "")

This gave me hope and then made me lose it just as quickly.

chatGPT:

=IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH(D$2:D$4,A2))), SUMPRODUCT(--ISNUMBER(SEARCH(E$2:E$4,A2)))=0), "TRUE", "")

Didn't work either

Tried referencing, but got lost in it. If someone can figure out what I should put into the TABLE1 space, aka. paste their function, that would be helpful.

Values	Include	Exclude	Expect
AMU_CMD	FF	FFCD
FF_FMIT	VV	VVC	TRUE
FFCD
VV			TRUE
LOL
TCID_VVC
RMI

Answer 1

You need to test that ANY of the Includes are present and NONE of the Excludes are present.

I used Named Ranges to simplify things and make the formulas easier to understand, but you could use normal addressing if you preferred.

=IF(OR(ISNUMBER(FIND(Include,A2)))*AND(NOT(ISNUMBER(FIND(Exclude,A2)))),TRUE,"")

where Include and Exclude are named ranges.

Enter the formula in, for example, D2 and fill down.

In Excel 2010, this formula may need to be array-entered:

---

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl-shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

In 365 you could enter this formula in a single cell and the results will spill down:

=BYROW(Values,LAMBDA(arr, IF(OR(ISNUMBER(FIND(Include,arr)))*AND(NOT(ISNUMBER(FIND(Exclude,arr)))),TRUE,"")))

Note that Values would be the named range of your first column.

Also note that the FIND function is case-sensitive. If you need the matching to be case-insensitive, then replace it with the SEARCH function.

Answer 2

If the Include/Exclude pairs are matched by rows, then the first formula should work (it works for me). It can be simplified a bit:

=IF(SUMPRODUCT(ISNUMBER(SEARCH($D$2:$D$3, $A2)) * ISERROR(SEARCH($E$2:$E$3, $A2))), "True", "")

I didn't verify the other formulas.