+ Reply to Thread
Results 1 to 7 of 7

Match Column A + B, Then Transpose Data From Columns, C, D, E Based Off Match

  1. #1
    Registered User
    Join Date
    12-10-2012
    Location
    United States
    MS-Off Ver
    Microsoft Excel for Mac 2011
    Posts
    3

    Match Column A + B, Then Transpose Data From Columns, C, D, E Based Off Match

    Hi,

    Looking to match columns A and B using Vlookup function. Both columns have same info, just in different rows

    Example:
    Column A Column B
    1 2
    3 4
    2 3
    5 6
    6 5

    Then after matching Column A with Column B, want to extract corresponding Columns based off of the match.

    Example:

    After matching Columns A + B,
    Column A Column B Column C Column D Column E
    3 3 123 MAIN STREET HAPPYTOWN COUNTRY
    4 4 123 HAPPY AVE HAPPYVILLE COUNTRY

    I am probably wrong about using a Vlookup function, but I want to find a way to do this. Can this be done? And if so, what is the function?


    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Match Column A + B, Then Transpose Data From Columns, C, D, E Based Off Match

    from the sounds of it, you could use either vlookup or index/match, with vlookup, you just have tyo change the column number

    if this doesnt work, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-29-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Match Column A + B, Then Transpose Data From Columns, C, D, E Based Off Match

    I would probably try using "Concatenate" which combines the data for 2 cells into one. You could do that for both the top and bottom sections then do a simple Vlookup:

    Column C would look like this (going down)
    12
    34
    23
    56
    65

    You could insert the same formula in the bottom section and then would have 1 number to do a Vlookup with. Would that work?

  4. #4
    Registered User
    Join Date
    12-10-2012
    Location
    United States
    MS-Off Ver
    Microsoft Excel for Mac 2011
    Posts
    3

    Re: Match Column A + B, Then Transpose Data From Columns, C, D, E Based Off Match

    Excel Workbook Example.xlsx

    Ok here is what I came up with. It is similar to my example above, but with some different data.

    *I tried to use an INDEX and MATCH function, but I couldn't get the arguments right.


    Hope this helps.

  5. #5
    Registered User
    Join Date
    05-29-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Match Column A + B, Then Transpose Data From Columns, C, D, E Based Off Match

    I must be missing something, because it just appears that a simple Vlookup will solve your problem (well technically 3 in the given example). In workbook 1, just add columns for Street, Town and Country and vlookup the information.

  6. #6
    Registered User
    Join Date
    12-10-2012
    Location
    United States
    MS-Off Ver
    Microsoft Excel for Mac 2011
    Posts
    3

    Re: Match Column A + B, Then Transpose Data From Columns, C, D, E Based Off Match

    Is there a way to just do one vlookup that extracts all that info (Street, Town, and Country)?

    If not, I guess I'll just do 3 separate look ups.

    Thanks for your help everyone!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Match Column A + B, Then Transpose Data From Columns, C, D, E Based Off Match

    as suggested, either vlookup or index/match...

    =VLOOKUP(A21,$C$8:$F$13,2,FALSE)
    =INDEX($C$7:$F$13,MATCH($A21,$C$7:$C$13,0),MATCH(D$20,$C$7:$F$7,0))
    either will do what you want, adjust ranges as needed

    edit: based on your last post, try this...
    =VLOOKUP(A21,$C$8:$F$13,2,FALSE)&" "&VLOOKUP(A21,$C$8:$F$13,3,FALSE)&" "&VLOOKUP(A21,$C$8:$F$13,4,FALSE)

    you can do the same concatenation with the index/match

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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