Formula explanation

[anonymous]

Hello -

Can someone please explain how this formula works?
Specifically, the 2nd argument in the SEARCH() function:

SUMPRODUCT(--(ISNUMBER(SEARCH(" "&G1&" "," "&B1:B11&" "))))

As written, it works. Is it simply adding a leading and
trailing space to each cell in the lookup range? If I use
an argument like this: B1:B11, it fails.

I wish I could remember how I was able to figure this out
so that it worked!!!!!

Thanks

 

[Leo Heuser]

Hello

Hello -

Can someone please explain how this formula works?
Specifically, the 2nd argument in the SEARCH() function:

SUMPRODUCT(--(ISNUMBER(SEARCH(" "&G1&" "," "&B1:B11&" "))))

As written, it works. Is it simply adding a leading and
trailing space to each cell in the lookup range?

Exactly, and because the word to search for has been given a leading
and a trailing space, a word can be found, even if it's the first in the
cell
(no leading space) or the last one (no trailing space).

SEARCH returns the position of the first character of the word (if found)
If not found, the #VALUE! error is returned.

So searching B1:B11 might return this array:

{3,4,5,1,#VALUE!,#VALUE!,#VALUE!,4,#VALUE!,5,6}

ISNUMBER is searching this array, returning TRUE or FALSE,
depending on, whether a number or #VALUE! is found.
In this situation it returns this array:

{TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,TRUE}

-- (unary minus)

[I prefer adding zero. It has the same effect and may be easier to
understand:
0+(ISNUMBER(SEARCH(" "&G1&" "," "&B1:B11&" ")))]

forces Excel to calculate TRUE as 1 (one) and FALSE as 0 (zero), resulting
in this array:

{1,1,1,1,0,0,0,1,0,1,1}

SUMPRODUCT adds the elements of this array and returns 7, which is
the number of cells, in where the search word is found.

 

[anonymous]

-----Original Message-----
Hello


"anonymous" <[email protected]> skrev i en meddelelse

Hello -

Can someone please explain how this formula works?
Specifically, the 2nd argument in the SEARCH() function:

SUMPRODUCT(--(ISNUMBER(SEARCH (" "&G1&" "," "&B1:B11&" "))))

As written, it works. Is it simply adding a leading and
trailing space to each cell in the lookup range?

Exactly, and because the word to search for has been given a leading
and a trailing space, a word can be found, even if it's the first in the
cell
(no leading space) or the last one (no trailing space).

SEARCH returns the position of the first character of the word (if found)
If not found, the #VALUE! error is returned.

So searching B1:B11 might return this array:

{3,4,5,1,#VALUE!,#VALUE!,#VALUE!,4,#VALUE!,5,6}

ISNUMBER is searching this array, returning TRUE or FALSE,
depending on, whether a number or #VALUE! is found.
In this situation it returns this array:

{TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,TRUE
}

-- (unary minus)

[I prefer adding zero. It has the same effect and may be easier to
understand:
0+(ISNUMBER(SEARCH(" "&G1&" "," "&B1:B11&" ")))]

forces Excel to calculate TRUE as 1 (one) and FALSE as 0 (zero), resulting
in this array:

{1,1,1,1,0,0,0,1,0,1,1}

SUMPRODUCT adds the elements of this array and returns 7, which is
the number of cells, in where the search word is found.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.


.

Thanks Leo! Great explanation.

 

[Leo Heuser]

Thanks Leo! Great explanation.

You're welcome, whoever you are.(A name would have been nice)
Glad you could use it

LeoH