function - Excel Search returns #Value, but embedded in "Lookup" it works - why? -

function - Excel Search returns #Value, but embedded in "Lookup" it works - why? -

this links question 12299700 - want understand why search returns #value when used lone works when embedded lookup, , how can create work alone:

in cell a1 have string of text: "this sample text" in cells d1:d4 have words: "text, sample1, sample2, string" (all in separate cells)

i want see if string contains of words in range - don't need matching word/s returned.

using search function in empty cell in row 1 =search($d$1:$d$4,a1), returns 22. good! using in other row returns #value. why? if maintain formula in row 1, move range of words downwards d2:d5, #value. why? how can create search work cell , ranges, in cell?

(from reply question 12299700 know can utilize formula homecoming matched text, in cell of spreadsheet: =lookup(2^15,search($d$1:$d$4,a1),$d$1:$d$4) - includes term gives #value on own ... intriguing).

first question asked on stackoverflow - feedback appreciated.

the function search returns #value if searched string not found within text. function search expect 1 searched string. not array function itself. if gets range searched string, uses 1 of these range values searched string. 1 is, depends on position of formula.

within lookup case different. lookup array formula itself. gets search($d$1:$d$4,$a$1) { search($d$1,$a$1), search($d$2,$a$1), search($d$3,$a$1), search($d$4,$a$1) } results in array { #value, 21, #value, 21 } depending of if search finds string or not. lookup works such array strange. needs sorted array. works , gets lastly value lower or equal searched value.

if need utilize search array formula outside native array formula, have create array context entering formula [ctrl]+[shift]+[enter] instead of [enter]. formula gets curly braces around it.

in illustration have created such formula in cell e10.

if interested in how formulas work, should click fx , how single parameters comes through. , should utilize evaluate formula on formulas tab, in formula auditing group.

excel function