CODEWITHSUNDEEP
excelsearchexcel-formulasumproduct
user581580
user581580

Reputation: 3

Why does the SEARCH function works only in combination with SUMPRODUCT when searching for multiple strings?

I'm checking in Excel if a text in a cell in a column is within the text at another column.

Example text to search:

Column A

 1. a
 2. b
 3. c

Text to search within:

Column B

 1. aagg
 2. hgjk
 3. dhhd
 4. bahj
 5. adjd

This formula works:

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

But this one works only for the first cell and for the rest I get #VALUE! error:

={(SEARCH(A$1:A$3,B1))}

Column B

Also the second formula is only giving results for the first three cells, as many as the number of cells in column A. The last two are empty.

What makes SEARCH work with SUMPRODUCT but cannot work alone as an array formula?

Upvotes: 0

Views: 370

Answers (1)

Domenic
Domenic

Reputation: 8124

Let's assume we have the following data...

A1:A3

    a
    b
    c

B1

    oboe

In this case, SEARCH(A$1:A$3,B1) returns the following array of values...

#VALUE!
2
#VALUE!

If the formula is entered in a single cell, only the first value from this array gets transferred to this cell. So, in this case, #VALUE! will be display in the cell.

However, if you select three cells, then enter the formula, and then confirm with CTRL+SHIFT+ENTER, all three values from the array gets transferred to these cells.

Note, though, since A1:A3 is a vertical range of cells, you'll need to select a vertical range of cells in which to return these values. For example, you could select D1:D3, then while these three cells are selected enter the formula, and then confirm with CTRL+SHIFT+ENTER.

Now, for the interesting part. To return TRUE or FALSE, you'll need to first pass the array of values returned by SEARCH to ISNUMBER, and then you'll need to pass the array of values returned by ISNUMBER to the OR function, which in turn will return TRUE if at least one of the values returned by ISNUMBER is TRUE. Otherwise, it returns FALSE. So, you would use the following formula, which needs to be confirmed with CTRL+SHIFT+ENTER... 

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

Here's how the formula is evaluated...

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

=OR(ISNUMBER(SEARCH({"a";"b";"c"},"oboe")))

=OR(ISNUMBER({#VALUE!;2;#VALUE!}))

=OR({FALSE;TRUE;FALSE})

...which returns TRUE. By the way, ISNUMBER is needed here. We can't simply pass the array of values returned by SEARCH to the OR function. If we did, the OR function would return #VALUE!, since the array of values contains an error value, in this case #VALUE!. That's why we use ISNUMBER, so that any error value gets converted to FALSE before passing it the OR function. So the OR function will always get an array of values made up of TRUE and/or FALSE.

Hope this helps!

Upvotes: 1

Related Questions