VLOOKUP and Referencing a Range in a Formula
 

I have a formula that I am trying to execute which looks up the code/city in
Col-A and returns the REFERENCE to the corresponding arrays that are in Cols
B-F. The reference is then used to pull the contents of the array(s) into
another formula:

Col-A Col-B Col-C Col-D Col-E Col-F
TEXT (entered as arrays)

3777Chicago 2006 2007 2008 2009
3750Boston 2006 2007 2008
3737Detroit 2007 2008 2009
3750Denver 2008 2009 2008

The formula is in "Worksheet-A", while the above data is in a worksheet
called "REF_Table". I've been trying to use the code/city data in Col-A as a
"pointer" to the fiscal years data contained in each corresponding array.
I've tried every combination of MATCH, OFFSET, INDIRECT, INDEX that I can
think of to no avail. Any suggestions?? Thanx in advance.

- GTblearch

VLOOKUP and Referencing a Range in a Formula
 

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"GTblearch" wrote in message
...
I have a formula that I am trying to execute which looks up the code/city
in
Col-A and returns the REFERENCE to the corresponding arrays that are in
Cols
B-F. The reference is then used to pull the contents of the array(s) into
another formula:

Col-A Col-B Col-C Col-D Col-E Col-F
TEXT (entered as arrays)

3777Chicago 2006 2007 2008 2009
3750Boston 2006 2007 2008
3737Detroit 2007 2008 2009
3750Denver 2008 2009 2008

The formula is in "Worksheet-A", while the above data is in a worksheet
called "REF_Table". I've been trying to use the code/city data in Col-A
as a
"pointer" to the fiscal years data contained in each corresponding array.
I've tried every combination of MATCH, OFFSET, INDIRECT, INDEX that I can
think of to no avail. Any suggestions?? Thanx in advance.

- GTblearch

VLOOKUP and Referencing a Range in a Formula
 

Hi,
try
=sumproduct((a1=REF_Table!$A$1:$A$1000),REF_Table! b$1:b$1000)

change range to fit you needs

"GTblearch" wrote:

I have a formula that I am trying to execute which looks up the code/city in
Col-A and returns the REFERENCE to the corresponding arrays that are in Cols
B-F. The reference is then used to pull the contents of the array(s) into
another formula:

Col-A Col-B Col-C Col-D Col-E Col-F
TEXT (entered as arrays)

3777Chicago 2006 2007 2008 2009
3750Boston 2006 2007 2008
3737Detroit 2007 2008 2009
3750Denver 2008 2009 2008

The formula is in "Worksheet-A", while the above data is in a worksheet
called "REF_Table". I've been trying to use the code/city data in Col-A as a
"pointer" to the fiscal years data contained in each corresponding array.
I've tried every combination of MATCH, OFFSET, INDIRECT, INDEX that I can
think of to no avail. Any suggestions?? Thanx in advance.

- GTblearch