Range names in array formula

SamS · Mar 19th, 2002, 02:40 PM

How do I make a vlookup return a range name in an array table? I have a spreadsheet with several ranges that holds budgeting and forecasting data but only a single graph. The idea is to select a particular product from a drop down combo box and the graph displays only that product. To this end I have an array formula:
{=index(data_area,e62,0)}
which works well if the data is all contiguous.

Where I am striking trouble is when I try to use a vlookup instead of "data_array" to return a range name but I get returned the dreaded #Value, I know there should be a simple answer but can't seem to see it. Any help would be much appreciated.

Aladin Akyurek · Mar 19th, 2002, 05:11 PM

Quote:

On 2002-03-19 13:40, SamS wrote:
How do I make a vlookup return a range name in an array table? I have a spreadsheet with several ranges that holds budgeting and forecasting data but only a single graph. The idea is to select a particular product from a drop down combo box and the graph displays only that product. To this end I have an array formula:
{=index(data_area,e62,0)}
which works well if the data is all contiguous.

Where I am striking trouble is when I try to use a vlookup instead of "data_array" to return a range name but I get returned the dreaded #Value, I know there should be a simple answer but can't seem to see it. Any help would be much appreciated.

Not sure but is this what you are looking for?

=INDEX(INDIRECT(VLOOKUP(lookup-value,RangeList,2,0)),E62,0)

where Rangelist is a lookup table of ranges of inetrest.

Aladin

SamS · Mar 19th, 2002, 05:28 PM

Thanks Aladin, it worked perfectly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 02:40 PM	#1
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	How do I make a vlookup return a range name in an array table? I have a spreadsheet with several ranges that holds budgeting and forecasting data but only a single graph. The idea is to select a particular product from a drop down combo box and the graph displays only that product. To this end I have an array formula: {=index(data_area,e62,0)} which works well if the data is all contiguous. Where I am striking trouble is when I try to use a vlookup instead of "data_array" to return a range name but I get returned the dreaded #Value, I know there should be a simple answer but can't seem to see it. Any help would be much appreciated.

Mar 19th, 2002, 05:28 PM	#3
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	Thanks Aladin, it worked perfectly