+ Reply to Thread
Results 1 to 9 of 9

Reference to named range to be used in index match formula!! Help Please!!

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Reference to named range to be used in index match formula!! Help Please!!

    Hi there!!

    The following is the problem I am encountering on excel 2010:

    I have a quite complex worksheet and I named (static) some tables to scale down the complexity of the formula a bit.

    In particular, in one sheet I have 25 tables (one for every company) and I named them accordingly. In another sheet I would like to be able to use an index match formula to retrieve some values from those tables I named (contained in the other sheet).

    Now, no problem if in the index formula I write the name of the table as I created it, but I would like to make the process easier and create a reference to the name in another cell in the sheet. How can I do that?

    Example:

    Table name: "Company1" Sheet containing the table: "Sheet_Tables" Sheet containing the Index formula: "Sheet_Indexes" Formula that works: =index(Company1,match(...)

    I would like to: - Use a cell in Sheet_Indexes (let's say cell A1) to create a reference to the table name, so: A1= Company1 - use the reference in the index formula to get to the table name instead of writing the full name of the table in every index formula, so: index(A1,match(...).

    Does anyone know how to perform it?

    I am trying with some indirect formula but it's not really working..

    Thanks in advance!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Reference to named range to be used in index match formula!! Help Please!!

    Hi,

    INDIRECT should work, though not in cases where your named ranges/tables use certain functions, e.g. OFFSET.

    Can you post a sample indicating your issue, what you have tried so far and what type of error you are receiving?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Reference to named range to be used in index match formula!! Help Please!!

    Try

    =index(indirect(A1),match(...)

    Since you have defined name ranges, do not use "" with the INIDRECT formula
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    06-24-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Reference to named range to be used in index match formula!! Help Please!!

    Hi XOR LX and Ace_LX,

    Thanks for the reply!

    Here a short example:

    Table name: "Company1"
    Sheet containing the table: "Sheet_Tables"
    Sheet containing the Index formula: "Sheet_Indexes"
    Formula that works: =index(Company1,match(...)
    In cell A1 in Sheet_Indexes I use =Company1 (as a result the cell displays the first row of the table)
    In index formula I use (as suggested by Ace-XL) =index(indirect(a1),match(...) but it's not working (the result is #REF!)

    I think the problem might be how I set the formula in a1 rather than the index/indirect formula.

    Any suggestion??

    Thanks!!

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Reference to named range to be used in index match formula!! Help Please!!

    Aha..

    In cell A1 in Sheet_Indexes I use =Company1 (as a result the cell displays the first row of the table)
    Use simply Company1 (without the =) so that it displays Company1, the INDIRECT will work fine then

  6. #6
    Registered User
    Join Date
    06-24-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Reference to named range to be used in index match formula!! Help Please!!

    Okay, now I feel really dumb.....

    Indeed, that was the problem...thanks Ace_XL!
    I am not very comfortable with the INDIRECT formula as I do not understand properly how it works in some situation... May I ask you if you have any useful links that explain some cases to use INDIRECT?

    Thank you very much again for your help!

  7. #7
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Reference to named range to be used in index match formula!! Help Please!!


  8. #8
    Registered User
    Join Date
    06-24-2013
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Reference to named range to be used in index match formula!! Help Please!!

    Perfect!

    Thanks a again and have an EXCEL(lent) day!

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Reference to named range to be used in index match formula!! Help Please!!

    Agghh! German humour!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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