Is there a way to return an array value in excel through a function or
by reversing a function. I need to do an lookup of a name in a row,
and then have a cell from the row return it's corresponding column
array. e.g (row 1, cell 4 (Bob) = $D$1:$D$500.

I need to be able to use the result in another function like a Match/
Index

I have been able to produce the effect using substitute and address
embeded functions. But I cannot use the result as the array for a
Index/Match. I think because it's just text and not the actual defined
array.

I would like to use the result to fill in the $J$15:$J$509 array below

=INDEX($J$15:$J$509,MATCH(B6,$I$15:$I$509,FALSE),1 )

Do you mean

INDIRECT(ADDRESS(1,MATCH("lookup",1:1,0))&":"&ADDR ESS(500,MATCH("lookup",1:1,0)))

--
__________________________________
HTH

Bob

"bc4240" wrote in message
...
Is there a way to return an array value in excel through a function or
by reversing a function. I need to do an lookup of a name in a row,
and then have a cell from the row return it's corresponding column
array. e.g (row 1, cell 4 (Bob) = $D$1:$D$500.

I need to be able to use the result in another function like a Match/
Index

I have been able to produce the effect using substitute and address
embeded functions. But I cannot use the result as the array for a
Index/Match. I think because it's just text and not the actual defined
array.

I would like to use the result to fill in the $J$15:$J$509 array below

=INDEX($J$15:$J$509,MATCH(B6,$I$15:$I$509,FALSE),1 )

"Bob Phillips" wrote...
Do you mean

INDIRECT(ADDRESS(1,MATCH("lookup",1:1,0))&":"&ADD RESS(500,MATCH("lookup",1:1,0)))
....

If so, wouldn't

INDEX($A$1:$IV$500,0,MATCH("lookup",$A$1:$A$500,0) )

be preferable in terms of brevity, efficiency (only 1 MATCH call) and
use of only nonvolatile functions?

I had to leave you something to do <g

--
__________________________________
HTH

Bob

"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
Do you mean

INDIRECT(ADDRESS(1,MATCH("lookup",1:1,0))&":"&AD DRESS(500,MATCH("lookup",1:1,0)))
...

If so, wouldn't

INDEX($A$1:$IV$500,0,MATCH("lookup",$A$1:$A$500,0) )

be preferable in terms of brevity, efficiency (only 1 MATCH call) and
use of only nonvolatile functions?

On Jul 29, 3:12*pm, "Bob Phillips" wrote:
I had to leave you something to do <g

--
__________________________________
HTH

Bob

"Harlan Grove" wrote in message

...



"Bob Phillips" wrote...
Do you mean

INDIRECT(ADDRESS(1,MATCH("lookup",1:1,0))&":"&AD DRESS(500,MATCH("lookup",*1:1,0)))
...

If so, wouldn't

INDEX($A$1:$IV$500,0,MATCH("lookup",$A$1:$A$500,0) )

be preferable in terms of brevity, efficiency (only 1 MATCH call) and
use of only nonvolatile functions?- Hide quoted text -

- Show quoted text -

Bob Yes this did work for me after I looked at it twice I used the
address portion to populate a sepeate cell and then used the Indirect
to fill my range requirements for my Index function PERFECT
solution!!!!!! Thanks