Trying to understand how SEARCH work

[szakharov7723]

So I use the same database, the same cell, same formula. But somehow I get different behavior of formula when I evaluate it.
Here I use SEARCH as a single formula

{=SEARCH(List,E521)}

And I get only first item from the List (table).

But here I use SEARCH in combination of formula

{=INDEX(List,SUMPRODUCT(ISNUMBER([U]SEARCH(List,E521)[/U])*(ROW(List)-1)))}

And I actually get all items from the List.

What makes it change the behavior ?

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...isnt-allowed-while-count-why.html#post5361871

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

As you have already had an answer in the other thread, I am locking this one.