+ Reply to Thread
Results 1 to 5 of 5

Nesting 2 INDIRECT inside an INDEX MATCH - any ideas?

  1. 07-31-2013, 06:58 PM #1
    Sam the Monster
    Sam the Monster is offline
    Registered User
    Join Date
    07-31-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    2

    Nesting 2 INDIRECT inside an INDEX MATCH - any ideas?

    =INDEX(,INDIRECT("'["&B3&"]"&B4&"'!"&C3:C8),MATCH(D3,INDIRECT("'["&B3&"]"&B4&"'!"&A3:A8),0))

    B3=scores.xlsx
    B4=(worksheet name)
    C3:C8 should be on the scores.xlsx worksheet
    D3=the data I am looking for on the scores.xlsx workbook.
    A3:A8 should be on the scores.xlsx worksheet

    Unfortunately it does not seem to be looking up the value I had expected it to, instead it is returning #VALUE! ...

    Has anyone got any ideas as to why this is not working or another work around to the same effect.

    I am trying to run an index match on another workbook on multiple worksheets. The worksheets are numbered 1 through to 30 for each day of the month. So it would be easier for me to INDIRECT for the worksheet names.

    Thanks for the help!
    Sam the Monster
    Reply With QuoteRegister To Reply
  2. 07-31-2013, 07:58 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Nesting 2 INDIRECT inside an INDEX MATCH - any ideas?

    Hi and welcome to the forum

    indirect() does not work on closed workbooks

    Try installing the add-in morefunc
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 08-01-2013, 09:45 AM #3
    DavidJAllen
    DavidJAllen is offline
    Registered User
    Join Date
    06-22-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Nesting 2 INDIRECT inside an INDEX MATCH - any ideas?

    Sam,

    The syntax is index(array, row, column), column can be optional.

    Problem 1 is that you do not specify an array that index should deal you have a comma following index(
    Problem 2 is that the "row" returns a value based on indirect, I am assuming this is actually the array you want to do the look-up on. If it is this INDIRECT statement works fine, if not it needs to return a number
    Problem 3 is that the match indirect is not correct as the final range needs to be inside the quotes.

    I am assuming you just want to do =INDEX(ARRAY,ROW) in which case the following works for me, note I have scores.xlsx open

    =INDEX(INDIRECT("'["&B3&"]"&B4&"'!C3:C8"),MATCH(D3,INDIRECT("'["&B3&"]"&B4&"'!A3:A8"),0))

    One great trick is that if you highlight at bit of the formula and press F9 Excel will evaluate it, press <ctrl+z> to return to the formula. Using that method you can see what each bit of the formula is returning.

    Regards

    David
    Last edited by JBeaucaire; 08-14-2013 at 10:24 AM.
    Reply With QuoteRegister To Reply
  4. 08-02-2013, 07:41 AM #4
    Sam the Monster
    Sam the Monster is offline
    Registered User
    Join Date
    07-31-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003 and 2010
    Posts
    2

    Re: Nesting 2 INDIRECT inside an INDEX MATCH - any ideas?

    Hi David and FDibbins,

    Thank you for welcoming me to the forum and thank you for all of your help.

    I am a bit of a Greenhorn when it comes to Excel, however I am really getting into it. This forum seems to be an invaluable resource when in trouble. I will be sure to contribute in the future.

    Oh and David, thanks for the F9 trick that has helped me a lot this morning.

    Many Thanks,
    Sam the Monster
    Reply With QuoteRegister To Reply
  5. 08-14-2013, 10:25 AM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Nesting 2 INDIRECT inside an INDEX MATCH - any ideas?

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] any Ideas Why this INDEX MATCH formula doesnt work ?
    By ecelaras in forum Excel General
    Replies: 13
    Last Post: 11-30-2012, 06:55 AM
  2. [SOLVED] Need assistance on nesting an indirect function in a index / match formula
    By ghosters in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2012, 06:00 AM
  3. Excel 2007 : using MATCH inside of an INDEX formula
    By can2c in forum Excel General
    Replies: 2
    Last Post: 03-17-2010, 06:37 AM
  4. Multiple nesting lookup, index, match
    By Georgia Golfer in forum Excel General
    Replies: 11
    Last Post: 03-15-2010, 04:02 PM
  5. Nesting Index and Match Functions
    By Malone in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-16-2005, 06:55 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1