Alternative to ADDRESS function
 

In Excel 2007, I am using the following formula to search for and return all
cells in the array that meet the criteria (as opposed to vlookup, which only
returns one ):

=IFERROR(INDEX($A$5:$A$100,SMALL(IF($B$6:$B$101=$D $3,ROW($B$1:$B$96)),ROW(1:1))),"")

Then, using the ADDRESS and MATCH functions, I can return the address of all
the non ERROR values output from the formula above.

I need to reference a certain array of cells based upon the address of the
non ERROR values. For instance, if the first non ERROR cell is A5, then I
need to reference cells A5:Z7. If the next non ERROR cell is A10, then I
need to reference cells A10:Z12.

I need to reference those arrays because I need to use the MATCH and INDEX
functions, which require an array. If I try to put

=INDEX(ADDRESS(...):ADDRESS(...)...) that doesn't work.

I'm sure there are many alternate routes to get where I need to go, but I
can't seem to figure them out. Thanks for you help.

DoubleZ

Alternative to ADDRESS function
 

The Address() function returns a *text* reference.

Wrap it in the Indirect() function to make it a viable cell reference.

Say H2 contains 50.

=2*Address(2,8)

returns a #Value! error.

=2*Indirect(Address(2,8))

returns 100.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"DoubleZ" wrote in message
...
In Excel 2007, I am using the following formula to search for and return
all
cells in the array that meet the criteria (as opposed to vlookup, which
only
returns one ):

=IFERROR(INDEX($A$5:$A$100,SMALL(IF($B$6:$B$101=$D $3,ROW($B$1:$B$96)),ROW(1:1))),"")

Then, using the ADDRESS and MATCH functions, I can return the address of
all
the non ERROR values output from the formula above.

I need to reference a certain array of cells based upon the address of the
non ERROR values. For instance, if the first non ERROR cell is A5, then I
need to reference cells A5:Z7. If the next non ERROR cell is A10, then I
need to reference cells A10:Z12.

I need to reference those arrays because I need to use the MATCH and INDEX
functions, which require an array. If I try to put

=INDEX(ADDRESS(...):ADDRESS(...)...) that doesn't work.

I'm sure there are many alternate routes to get where I need to go, but I
can't seem to figure them out. Thanks for you help.

DoubleZ