Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Range names in array formula

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,635
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin, it worked perfectly

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •