+ Reply to Thread
Results 1 to 4 of 4

INDIRECT function together with INDEX MATCH

  1. #1
    Registered User
    Join Date
    04-22-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    59

    INDIRECT function together with INDEX MATCH

    Hi,

    I have a formula in an existing project

    =INDEX('Table 9'!B11:L107,MATCH(intResult!D12,'Table 9'!A11:A107,0),MATCH(intResult!C25,'Table 9'!B10:L10,0))

    This works fine for extracting the cell data from the array in Sheet!Table 9 the issue is there are 28 tables on 28 different sheets. The sheet names are 'Table 1', 'Table 2' and so on. At the moment I am having to duplicate the pair of sheets intResult and the Table sheet 28 times which is so wasteful

    I have a cell D5 on my intResult sheets which contains the correct table to be used (and so sheet name)

    I have seen mention of INDIRECT function that can pass the sheet name to the formula but I am unsure of the correct syntax and all efforts on my part so far result in REF#. If I could get this working I would only be in need of one intResult sheet and the Table sheets.

    The link also on this forum offering a soluction to a previous question is http://www.excelforum.com/excel-gene...ect-match.html

    Any help offered here would be most appreciated.
    Last edited by coasterman; 12-09-2011 at 11:07 AM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: INDIRECT function together with INDEX MATCH

    Try this

    =INDEX(INDIRECT("'"&intResult!D5&"'!B11:L107"),MATCH(intResult!D12,INDIRECT("'"&intResult!D5&"'!A11:A107"),0),MATCH(intResult!C25,INDIRECT("'"&intResult!D5&"'!B10:L10"),0))
    Audere est facere

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: INDIRECT function together with INDEX MATCH

    So if the sheetname, Table 9 is in D5, the...

    =INDEX(INDIRECT("'"&intResult!D5&"'!B11:L107"),MATCH(intResult!D12,INDIRECT("'"&intResult!D5&"'!A11:A107"),0),MATCH(intResult!C25,INDIRECT("'"&intResult!D5&"'!B10:L10"),0))

    although if the active sheet is intResult, you don't need to include that in the formula.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    04-22-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    59

    Re: INDIRECT function together with INDEX MATCH

    Thank you both, works perfectly! That should shave quite a chunk off the project size

+ 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