+ Reply to Thread
Results 1 to 6 of 6

index match (issue)

  1. #1
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    index match (issue)

    Hello

    Im trying to show data for individuals from a drop down list, and its based on a few criterias

    Im trying to make my index, match formula lookup
    a) look the year (i.e. 2010, 2011); person's name, revenue report to use.

    the part im struggling with is, how can i reference the person's name to be a match source?

    here is my formula:

    =INDEX(Table9,MATCH($D$7,INDEX(Table9,0,1),0)+1,MATCH($C12,INDEX(Table9,1,0),0))

    it works perfectly, but im not sure how i can make it reference the employee name

    D7 = year selection from drop down (2010, 2011)
    C12 = period i want data to show for (which week i want show i,e. P1W1, P1W2 etc)

    that portions works fine and provides me with correct info., however I have the person's name in cell D6, not sure how i can incorporate that into the formula?

    i tried the following, but receving an error

    =INDEX(Table9,MATCH($D7,INDEX(Table9,0,1),MATCH(D6,INDEX(Table9,0,1)+1,MATCH(C$11,INDEX(Table9,1,0),0))))

    thank you!
    Last edited by step_one; 05-16-2011 at 02:58 PM.

  2. #2
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    194

    Re: index match (issue)

    Please post a sample workbook.

  3. #3
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index match (issue)

    Hello

    attached is a sample workbook.

    As you can see, I'm able to get the data, but if i change the name to another person, it does not change.

    Thanks mate.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index match (issue)

    any suggestions? thx you.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,191

    Re: index match (issue)

    You'll need to concatenate Year and Person in Table 9. I inserted it after C, making it the 3rd column in Table9 So in D7, = B7&C7

    Then your formula becomes in D11 of Performance

    =INDEX(Table9,MATCH($D$7&$D$6,INDEX(Table9,0,3),0)+1,MATCH(C11,INDEX(Table9,1,0),0))
    Does that work for you?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index match (issue)

    Oh jeez,

    Thanks, I was looking up online but wasnt sure how to combine them both; thank you so much. saved me a so much hassle and worrying!

    cheers mate!

+ 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