+ Reply to Thread
Results 1 to 4 of 4

Struggling to understand the Index/Match functions

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    denver, co
    MS-Off Ver
    Excel Mac 2011
    Posts
    15

    Struggling to understand the Index/Match functions

    I'm trying to reference two columns (A & B) in Worksheet A, match them with two columns (A & B) in Worksheet B; then return the Value of Worksheet B (Column C), into Worksheet A (Column C)

    Hope that makes sense.
    I've does some searching for using VLOOKUP function to do this, and most of my searching ends up in someone using an Index/Match function; so I'm guessing vlookup isn't the solution.
    Here is the formula I tried using to accomplish this (in Column C, of Worksheet A). I'm sure it's a simple solution and I know I'm doing something wrong; but I can't figure it out.
    =INDEX(WorksheetB!$A$2:$A$10,MATCH(1,IF(WorksheetB!A$2:A$10=A2,IF(WorksheetB!B$2:B$10=B2,1)),0))
    I'm not sure if I'm heading in the right direction. I'm guessing because there's no reference to Column C in the function it doesn't know where to look. I'm not entirely sure. Looking for guidance.
    Thank you!


    Worksheet A
    Column A Column B Column C
    1 7 (worksheet B value)
    2 7 (worksheet B value)
    3 7 (worksheet B value)
    1 11 (worksheet B value)
    2 11 (worksheet B value)
    3 11 (worksheet B value)
    1 2 (worksheet B value)
    2 2 (worksheet B value)
    3 2 (worksheet B value)

    Worksheet B
    Column A Column B Column C
    1 7 45
    2 7 15
    3 7 25
    1 11 15
    2 11 35
    3 11 70
    1 2 40
    2 2 50
    3 2 5

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Struggling to understand the Index/Match functions

    Using your example, you have to first create a unique identifier for each row in by combing the values of Column A and Column B. Then use that in an Index/ Match function. Also it's an array formula so you have to enter it using Ctrl + Shift+ Enter, not just enter. If you do it correctly then you get curly brackets around your formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Entered as CSE and drag down.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    10-17-2012
    Location
    denver, co
    MS-Off Ver
    Excel Mac 2011
    Posts
    15

    Re: Struggling to understand the Index/Match functions

    Thank you abousetta. That should help me in the future too.

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Struggling to understand the Index/Match functions

    Glad I could be of assistance.

    Good luck.

    abousetta

+ 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