+ Reply to Thread
Results 1 to 4 of 4

Nested INDIRECT in INDEX/MATCH function

  1. #1
    Registered User
    Join Date
    04-29-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    17

    Nested INDIRECT in INDEX/MATCH function

    Hello,

    I'm trying to pull data from a document (2014 Source Data) into a new workbook. I'm would like to have my INDEX/MATCH function be dependant on a cell value in this new workbook. Below is how the existing formula is setup.

    ={(INDEX('[2014 Source Data.xls]January 2014'!$C$2:$C$678,MATCH(1,('[2014 Source Data.xls]January 2014'!$B$2:$B$678=$C3)*('[2014 Source Data.xls]January 2014'!$H$2:$H$678=$D3&" "&$F$2),FALSE)))}

    I want the sheet name in the formula to change based on what I choose from a drop down (different months). I've tried using INDIRECT but can't seem to figure it out. The source document will always be the same, it's just the sheet name that changes.

    Any help is greatly appreciated.

  2. #2
    Registered User
    Join Date
    02-12-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010 | 2013
    Posts
    43

    Re: Nested INDIRECT in INDEX/MATCH function

    Hi there,

    This site should help you tackle your problem.

    http://www.contextures.com/xlFunctions05.html --> Look under " Create a Reference to a Different Sheet "

    Note: If worksheet is not present (i.e. not created), will return #REF! --> You can use the IFERROR function to return like " Missing Tab " as opposed to showing the error.

    Hope it helps!
    Last edited by Redcoal; 02-22-2014 at 03:30 AM.

  3. #3
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    170

    Re: Nested INDIRECT in INDEX/MATCH function

    Assuming your drop down menu is in cell A1, your formula should like something like

    =INDIRECT("'"&$A$1&"'!$C$2:$C$678")

    Obviously just change the cell reference and range based on what you need.

  4. #4
    Registered User
    Join Date
    04-29-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Nested INDIRECT in INDEX/MATCH function

    Thanks Redcoal and cffndncr. I ended up using something I found on the site you sent me to Redcoal (awesome site btw). It directed me to essentially what you posted cffndncr. Thanks to both of you!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Indirect with Match/Index function
    By jyothijayanna in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-18-2013, 09:39 PM
  2. INDEX function with nested INDIRECT reference
    By jharris63 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-13-2013, 11:26 AM
  3. [SOLVED] Indirect Function with Vlookup vs. Index/Match
    By Jayule in forum Excel General
    Replies: 4
    Last Post: 07-02-2012, 12:01 PM
  4. INDIRECT function together with INDEX MATCH
    By coasterman in forum Excel General
    Replies: 3
    Last Post: 12-09-2011, 11:06 AM
  5. Nested Function using MATCH and/or INDEX
    By ExcelJunkie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-26-2006, 03:54 PM

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