Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile From cell in sheet 1 look up in sheet 2 and pull 2 cells

    Excel 2010
    I have two worksheets. I would like use each cell in a particular column and for each cell look up a match in sheet number 2 and pull information from two adjacent cells in the same row.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Captain,

    Using Index and Match, you can do what you want to achieve:

    In the first adjacent cell (B2) place the following formula:

    =INDEX(Sheet2!$A$2:$C$7,MATCH(Sheet1!$A2,Sheet2!$A $2:$A$7,0),2)

    In the second adjacent cell (C2) place the following formula:

    =INDEX(Sheet2!$A$2:$C$7,MATCH(Sheet1!$A2,Sheet2!$A $2:$A$7,0),3)

    Then copy down

    Sheet 1:

    Index_Match3.png

    Sheet 2 with the data:

    Index_Match4.png

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2014-12-21 at 23:19. Reason: added file

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, if you have a lot of data, I would suggest putting the MATCH part into a separate cell and then refer to that from both INDEX formulas. I'd also only use one column for the INDEX part:
    B2: =MATCH($A2,Sheet2!$A$2:$A$7,0)
    C2: =INDEX(Sheet2!B$2:B$7,$B2)
    and then copy across to D2 and fill down.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Instead of having 3 columns (B,C,D) in addition to a column for the look up values (A) as suggested above, I think a better alternative would be to eliminate the match altogether.

    Column A would have the lookup values (1,3,2,6,5,4)
    B2 =INDEX(Sheet2!B$2:B$7,$A2)
    C2 =INDEX(Sheet2!C$2:C$7,$A2)

    Copy columns C and D down

    Index_Match5.png

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Agreed - as long as your lookup values are the positions, of course.
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

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