CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Shamvil U. Gases
Shamvil U. Gases

Reputation: 79

=ISNUMBER(SEARCH()) formula not working properly

Basically, im trying to search if values from column b is contained in cells on column a

https://imgur.com/UevybZP

I am currently using the formula 

=ISNUMBER(SEARCH(B1,$A:$A))

and using it inside a conditional formatting to highlight the cells in column A that contains strings from column B. But it is not highlighting the correct cells

any advice?

Upvotes: 0

Views: 3121

Answers (3)

Terry W
Terry W

Reputation: 3257

Highlight column A (or the relevant range in column A starting cell A1) with the first cell (which is A1 in this case) as the active cell, use the following formula as the conditional formatting rule:

=(SEARCH($B1,$A1)*(LEN($B1)>0))>0

The logic is to first search the given sub-string from the main string, then multiple the result by LEN($B1)>0 to exclude the result of 1 returned for blank cells in column B.

Demo

Note: Conditional Formatting works in array fashion so even though the formula only looks at values in the first row of the range, as long as you use the relative (or in some cases absolute) cell references correctly and highlight the result range correctly before setting up the rule, the rule will be applied across in the same way as for the first row of the array as demonstrated in this example.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Problem is that your ISNUMBER(SEARCH(…. formula is returning an array of values {FALSE;TRUE;FALSE;FALSE;...} one return for each item in within_text. You need to know if any of those items match.

So, with your formula, consider the array formula modification

=OR(ISNUMBER(SEARCH(B1,$A:$A)))

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

If you don't like to use the CSE entry method, you could use this formula which will return zero for no matches, or be non-zero for any matches:

=SUMPRODUCT(-ISNUMBER(SEARCH(B1,$A:$A)))

Upvotes: 1

dotNET
dotNET

Reputation: 35400

Excel's SEARCH function is used to find the position of one string within another string. Generally you use it like this:

=SEARCH("String A", "A Longer String Containing String A")

This will return the character index where first string starts within the second string, which in this case would be 28.

What you really need is a VLOOKUP. Since you're doing a textual search (substring), you need your range to be of text type instead of number.

You should do the following:

  • Add an extra column to the right of Column A and use TEXT function to convert entries to textual form:

    =TEXT(A1, "@")

  • Now you can use VLOOKUP to perform a substring-match in this textual range. VLOOKUP supports wildcards when you do not ask it to perform an exact match (4th argument should be FALSE). Here is your formula then:

    =VLOOKUP("*" & C1 & "*",$B:$B,1,FALSE)

Note that I have passed column B (textual column) as the lookup range, whereas C1 is the cell containing the text that you want to search.

This method also has the additional advantage that it returns the actual matched entry from the range so you don't have to find it manually.

Once you have your results, you can apply conditional formatting to it.

Upvotes: 0

Related Questions