+ Reply to Thread
Results 1 to 6 of 6

Problem with INDEX, INDIRECT, MATCH, MATCH

  1. #1
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Problem with INDEX, INDIRECT, MATCH, MATCH

    I have been unable to get the Indirect portion of a formula to work.

    The following formula works but when I tried to include an Indirect Index all I get is #REF!

    Please Login or Register  to view this content.

    This is how I tried to add the Indirect portion.

    Please Login or Register  to view this content.
    Thanks

    Jim O
    Last edited by JO505; 09-01-2011 at 05:51 PM.

  2. #2
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    643

    Re: Problem with INDEX, INDIRECT, MATCH, MATCH

    Can you attach a file? Hard to debug a formula with no context.

  3. #3
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Problem with INDEX, INDIRECT, MATCH, MATCH

    I will send a file as soon as I can put together a sample. The actual file is a little involved so I will strip out the unnecessary data.

    As an aside the first formula works but I want to use Indirect inplace of the '2011'! value so I can easily refference different years (sheet tabs) from a dropdown list. The $L$3 cell will contain the dropdown list, all other refferences would remain the same.

    Jim O

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Problem with INDEX, INDIRECT, MATCH, MATCH

    Hello,

    the Indirect() statement is not formed properly.

    INDIRECT($L$3&"!"&DJ$9:DJ$24)

    This will take the content of cells DJ9 to DJ24 and will try to interpret that as a range. I think you really want

    INDIRECT($L$3&"!DJ$9:DJ$24")

    Try

    =INDEX(INDIRECT($L$3&"!DJ$9:DJ$24"), MATCH($O$3,INDIRECT($L$3&"!$A$9:$A$24"),0), MATCH($M18,INDIRECT($L$3&"!DJ$3"),0))

  5. #5
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Problem with INDEX, INDIRECT, MATCH, MATCH

    teylyn,

    Here is a sample file that I hope will make things clearer.


    Jim O
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Problem with INDEX, INDIRECT, MATCH, MATCH

    teylyn,

    Once I got my refferences in order your formula works perfectly.

    Thank you and bentleybob very much for your time and input.

    Jim O

+ 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