Index and Match Problem
Results 1 to 4 of 4

Thread: Index and Match Problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index and Match Problem

    Hi there,
    I hope you can help. am having some problems getting my head around Index and Match.

    I have a requirement to pull data from a specific cell in a sheet based on two Criteria.

    The Criteria are Week number, and Project number, and I want to return a specific weeks info.

    in my spreadsheet I have the following:
    Column A1:A20 lists the Project numbers. Cols B1 to M1 are the week numbers. Cells B2:M20 are the updates.

    The master sheet works on a week number and project number basis - I want to say: If Project ="22" and Week Number="39", then display that cells info.

    I hope this makes sense?

    Thanks
    Scott

  2. #2
    New Member
    Join Date
    Oct 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index and Match Problem

    lol - 3 edits later because I cant do it right the first time:
    What you are lookng forI think is a straight up index where your row of project crosses with your week number.

    Index(A1:M20,match(22,A1:A20,0),match(39,A1:M1,0))

    You could replace the 22 and 39 with a cell reference that you could change for a different project and week.

    If you had data in 2 columns as mentioned above (Column A = Project, Column B = Week, Column C = other data) then it would be the following to return that "other data" in column C associated with 22 and 39

    Index(A1:C20,match(1,(A1:A20=22)*(B1:B20=39),0),3)
    Last edited by holystoner; Oct 1st, 2014 at 04:36 PM.

  3. #3
    New Member
    Join Date
    Jul 2014
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index and Match Problem

    Thanks holystoner, it works a treat!!

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Index and Match Problem

    E1: 22 (a project number)
    F1: 39 (a week number)

    G1, just enter:

    =INDEX($B$1:$M$20,MATCH(E1,$A$2:$A$20,0),MATCH(F1,$B$1:$M$1,0))

    Or equivalently:

    =VLOOKUP(E1,$A$2:$M$20,MATCH(F1,$A$1:$M$1,0),0)
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •