Results 1 to 8 of 8

Thread: Index(?)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Index(?)

    Column A, Rows 1-10, Worksheet 1 are the letters A-J. Column A, Rows 1-10, Worksheet 2 are the letters A-J, Column B, Rows 1-10, names of clients. What I would like to do on Worksheet 1, column C, is "match" the client name on Worksheet 2 with the associated letters on Worksheet 1. For example, Column A, Row 1, Worksheet 1 contains A. Column A, Row 1, Worksheet 2 contains an A as well, and Column B, Row 1 contains the name "John". What I would like to do is insert a formula in Worksheet 1, Column C, Row 1 that matches the two As, and would result in the name "John" being inserted.

    Any help? Thanks in advance.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    JL,

    This should do the trick:
    =INDEX(Sheet2!A1:B10,MATCH($A1,Sheet2!A1:A10,0),2)
    Place the above formula in cell C1 of Sheet1 and then fill down.
    indexmatch.JPG
    Of course you can improve the formula by naming the range Sheet2!A1:A10 and Sheet2!A1:B10 and using the names in the formula.
    HTH
    JLKIRK-IndexMatch.xlsx
    Last edited by RetiredGeek; 2014-03-19 at 09:08.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A shorter version of the index/match is to index on only 1 column:
    =INDEX('Worksheet 2'!$B$1:$B$10,MATCH(A1,'Worksheet 2'!$A$1:$A$10,0))

    But in this case the more direct way is to use a VLOOKUP:
    =VLOOKUP(A1,'Worksheet 2'!$A$1:$B$10,2,0)

    Steve
    PS: RG, I think locking the cell ranges is preferred in this instance otherwise if the order is changed you may not find a match (if the appropriate match is on a row < low your lookup value is on)
    Last edited by sdckapr; 2014-03-19 at 12:00.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by sdckapr View Post
    PS: RG, I think locking the cell ranges is preferred in this instance otherwise if the order is changed you may not find a match (if the appropriate match is on a row < low your lookup value is on)
    Exactly! That's why I suggested using Named Ranges.

    BTW: I didn't suggest VLookup because the OP didn't specify that the key letters would always be in alpha order.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Exactly! That's why I suggested using Named Ranges.
    But even without named ranges, you can lock in the col and rows of the range so they range will not vary when the formula is copied. Your formula uses a different range for each row and if the value being looked is in a row > the row it is occupied in the table, it will not find the match...[for example if you the letter "A" in cell A2 of Sheet1, a match will not be found since it looks in Sheet2!A2:A11 so it misses the "A" in cell Sheet2!A1]

    I didn't suggest VLookup because the OP didn't specify that the key letters would always be in alpha order.
    I did not presume that they would be. Vlookup does not require them to be in Alphabetical order. Just like MATCH it can be used for an exact lookup with the optional parameter of zero ("0") at the end.

    Steve
    Last edited by sdckapr; 2014-03-19 at 16:19.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    You're correct the search ranges should be locked down. I was in a hurry to get out the door to pick up my wife's aunt at the airport an hour and a half away and wasn't thinking that filling down would change the references. Thanks for the reminder.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi RG

    What speed were you doing to pick up your wife's aunt? Did you get there on time? Did you have to carry the luggage? How's your back? And let's get our priorities right here. You had a compelling choice between answering an Excel question, or obeying the boss.

    I'm still in hospital. Day 10 I think. It must be the morphine.

    zeddy

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy.

    In order:
    60-70 MPH
    Yes
    Yes
    Great!!!!!!
    No choice! If Momma ain't happy Nobody's happy!
    Got any extra Morphine?

    Get well SOON!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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