+ Reply to Thread
Results 1 to 4 of 4

Named range lookup using Index and match

  1. #1
    Registered User
    Join Date
    03-01-2007
    Posts
    2

    Named range lookup using Index and match

    First post and first wall i've hit were searching forum/google hasn't helped :/.

    To avoid a lot of nasty nested if's I have setup several named ranges and I'm trying to use Index and match to match the relevant named range contined in an array of named ranges (VB can probably do this easier but I'm trying to stay excel functions).

    eg
    So user choices 2 variables in 2 seperate cells which contain 3 and 4 options each.

    calculation lower down grabs these two variables and goes and looks in one named range which contains an array of about 2 dozen named ranges. It returns the correct named range...except its just a string I can't get excel to actually refrence the range instead it just outputs the name (I want it to display the named range as a list).

    How do I get excel to use the range rather than just output the name of the range??

    Digram sort of
    User List A: 1,2,3= user picks 3
    User List B: A,B,C = user pick B

    formula goes and checks array using index and match
    A B C
    1 1A 1B 1C
    2 2A 2B 2C <<named range names
    3 3A 3B 3C

    it then returns 3B in the cell, how do I get it to use it as named range returning that range as a list ie below

    3B contains
    Six
    Seven
    Eight

    the named range is working as I can get it to list okay by doing a basic =3B in Data Validation

    sorry if I've repeated myself 3 times above and thanks for any help, also apologises if this obvious I'm pretty new to excel.

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Sirrob01
    First post and first wall i've hit were searching forum/google hasn't helped :/.

    To avoid a lot of nasty nested if's I have setup several named ranges and I'm trying to use Index and match to match the relevant named range contined in an array of named ranges (VB can probably do this easier but I'm trying to stay excel functions).

    eg
    So user choices 2 variables in 2 seperate cells which contain 3 and 4 options each.

    calculation lower down grabs these two variables and goes and looks in one named range which contains an array of about 2 dozen named ranges. It returns the correct named range...except its just a string I can't get excel to actually refrence the range instead it just outputs the name (I want it to display the named range as a list).

    How do I get excel to use the range rather than just output the name of the range??

    Digram sort of
    User List A: 1,2,3= user picks 3
    User List B: A,B,C = user pick B

    formula goes and checks array using index and match
    A B C
    1 1A 1B 1C
    2 2A 2B 2C <<named range names
    3 3A 3B 3C

    it then returns 3B in the cell, how do I get it to use it as named range returning that range as a list ie below

    3B contains
    Six
    Seven
    Eight

    the named range is working as I can get it to list okay by doing a basic =3B in Data Validation

    sorry if I've repeated myself 3 times above and thanks for any help, also apologises if this obvious I'm pretty new to excel.
    Hi,

    I think what you are looking for is something like

    =MATCH("B",INDIRECT(F2),0)

    where F2 is the selected name for the range (say A1:A50 and contains a cell with B).

    If not, I'll try again.
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    03-01-2007
    Posts
    2
    Bryan thanks for that works like a dream and for the super fast reply

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Sirrob01
    Bryan thanks for that works like a dream and for the super fast reply
    good to see, and thanks for your response.

    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1