SEARCH function returns array if first argument is array--Huh?

In answering another thread, I discovered that function SEARCH appears to return a sort of an array. This appears to be undocumented behavior for this function. Look at column K in the attached file.

Stranger yet, when the same formula is used for conditional formatting, it appears to iterate through the entire range for a match in the second argument. Also illustrated in attached. Normally, when a function specification calls for a single value as an argument, using a range will cause only the first item in the range to be used as the argument.

Is this intended behavior or a side effect of how Excel is built? It certainly is not evident in Microsoft's Help page on SEARCH. Do you have an explanation of this that is sufficiently rigorous to be able to use this feature predictably?

6StringJazzer,

The SEARCH function by itself won't return an array, which is displayed in your example. The named range GOOD has the letters "a", "b", "c", "d", and "e" and all of the cells you're testing the formula on contain the text "test", yet only K5 recognizes that an "e" is within the word "test" and thus displays the correct answer of 1. That happens because when SEARCH is fed an array, it uses the nth value in that array where n = the row number. You can test this by changing where the "e" is entered in your named range GOOD.

If the formula is array-entered, then it will properly get the count for each value in the array. Excel considers all Conditional Formatting formulas to be both array-entered and volatile, which is why the conditional formatting highlights all of the cells in column J.

afaik conditional format treats things in most circumstances an array formula
http://stackoverflow.com/questions/8...eadsheet-files

Hello 6String,

I think this behaviour is normal for many Excel functions - when you use a range in the place of a single value an array is generated, so your

=SEARCH(GOOD,J1)

generates an array as large as GOOD

.......but you get different types of behaviour when you wrap COUNT function around that depending on whether you "array enter" the formula or not

for your example if you array enter

=COUNT(SEARCH(GOOD,J1))

you will get the same result (1) wherever you enter it.....but if you don't "array enter" the formula then the "displayed" value of SEARCH is the one that appears on the same row, so you get 1 on the "e" row (you can see similar behaviour if you use a range as the first function of MATCH, for instance).

In conditional formatting you don't have to use any special key combination, formulas that might be "array formulas" are treated automatically that way so the behaviour is the same as that I noted above, i.e. 1 is returned for every row in your example hence all rows are formatted

Edit: I see I'm essentially repeating what Tigeravatar and Martin have said, apologies - something new then (!)..... you refer to VLOOKUP in the other question, that's slightly different because VLOOKUP doesn't generate an array when a range is used as lookup value (unlike LOOKUP or MATCH) so in that case the VLOOKUP gives results based on the row in which it resides......