Check range for string value existing in other range (with wildcards at either end)

0
votes

I need to search a range of cells for string values. Initially I had been hardcoding the criteria values into the formula and using COUNTIF, as below, which would return a value TRUE if the text string appeared anywhere in the search range.

=COUNTIF(A1:D1, "*VALUE1*") + COUNTIF(A1:D1, "*VALUE2*") + ... <0

However the list of criteria values I need to search has grown and hardcoding them is no longer viable. I know that COUNTIF criteria cannot reference a range, and needing wildcards also makes it tricky.

Anyone aware of any neat solutions?

Thanks

3
excelexcel-formula
Do you want to put your VALUEs in a separate column ??? – Gary's Student
If a string contains both VALUE1 and VALUE2 your formula counts it twice, is that what you want ?? – Gary's Student
If a string contains both, all I need is a TRUE result. If a string is "xxxValue1" is in my search range I need it to register as TRUE also. – Sean

3 Answers

1
votes

You can try SUMPRODUCT with SEARCH functions:

=SUMPRODUCT(--ISNUMBER(SEARCH($G$2:$G$3,$A$1:$D$1)))>0

enter image description here

1
votes

Try the following:

=SUMPRODUCT(COUNTIF(A1:D1,"*"&F1:F2&"*"))

Where F1:F2 references your range with criteria. As with the other answer, if both values occur in a single cell they will both be counted.

enter image description here

N.B.:If you need to compare to 0 just use =SUMPRODUCT(COUNTIF(A1:D1,"*"&F1:F2&"*"))>0 to return TRUE or FALSE

1
votes

Just throwing my hat in:

=OR(ISNUMBER(MATCH("*"&F1:F2&"*",A1:D1,0)))

Depending on ones version this my need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode:

enter image description here