Dear Harlan

Thank you for your crucial input re the ADDRESS wrapping. I have now found
my solution to this step of my spreadsheet problem. I do now have a new
problem but if I don't find an answer in the archive of answers on this forum
I will raise a new thread.

Best regards

Philip
--
Graewood Business Services, Kwinana, Perth, Western Australia


"Harlan Grove" wrote:

Philip Hunt <Philip wrote...
....
The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me
my expected value of 3. I need, though, to have a formula of this format
across 60 columns x 2000 rows =120,000 cells. The search value changes for
each row, and the row in the search area changes for each column. There are
therefore in fact 120,000 similar formula calculations required, but they are
slightly different in each case. . . .
....
As an extension of the above what I really want is not the SUM but in fact
the 'string' of zeroes and ones that the arrayed ISERROR produces to be
placed in the cell as a text string. I have tried

=TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1))), "0")

but all I get is zero, rather than the desired
"000000000000000010000000000000001000000000100000 0000000000000".
....

Unfortunately, the correct result is just "0" or "1". Your IF(..)
expression returns an *ARRAY* of numbers, e.g.,
{0,0,0,0,1,0,1,0,0,0,...}, and passing that through the TEXT function
only converts the array of numbers into an array of strings
{"0","0","0","0","1","0","1","0","0","0",...}. Excel will only display
one entry from either the array of numbers or the array of strings in
a given single cell. You'd need an add-in function to concatenate all
the 0s and 1s into a single string.

Facts established towards 'building of formula'

[reformatted - removing unnecessary braces and parenteses]
=SUM(
IF(
ISERROR(
FIND(
B751,
E4:BL4
)
),
0,
1
)
)

works as I want it to.
....

So would

=COUNT(FIND(B751,E4:BL4))


[reformatted - removing unnecessary parentheses]
=ADDRESS(
INDIRECT("R249C"&COLUMN(),FALSE),
5,
4,
1
)
&":"
&ADDRESS(
INDIRECT("R249C"&COLUMN(),FALSE),
64,
4,
1
)

gives me the text answer E4:BL4 when placed in the column that has the
number 4 in its cell in row number 249.

If you really want the text reference, you could reduce this to

=MID(CELL("Address",($A$1,OFFSET($E$1,D249-1,0,1,60))),6,32)

for the cell in column D.

BUT
when placing these 'tested' elements in their appropriate places in the
formula, in replacement of B751 and E4:BL4, as follows €“

[reformatted - yada yada yada]
=SUM(
IF(
ISERROR(
FIND(
INDIRECT("$B"&ROW()),
ADDRESS(
INDIRECT("R249C"&COLUMN(),FALSE),
5,
4,
1
)
&":"
&ADDRESS(
INDIRECT("R249C"&COLUMN(),FALSE),
64,
4,
1
)
)
),
0,
1
)
)

the formula does not work.

You forgot to wrap the ADDRESS(..)&":"&ADDRESS(..) inside it's own
INDIRECT call. It's just literal text without that INDIRECT call, so
the FIND call is going to treat it as a single text string rather than
refer to the cells in the range for which it's the text reference.

This could be simplified substantially. It's unclear where your
formulas are in relation to the cells in row 249 that you're
accessing, but it looks to me like an array formula of the form

=COUNT(FIND($B<row,OFFSET($E$1,<col$249-1,0,1,60)))

would return the proper result.