Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Retrieve 2 columns of data to the left of lookup value on another sheet.

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Retrieve 2 columns of data to the left of lookup value on another sheet.

    I have 2 workbooks, the first one has empty columns and I want to pull that data from another workbook. They look like this:

    A B C
    bob@comic.com
    geoff@live.co.uk
    bobby@live.co.uk
    bill@hotmail.com
    wes@sky.com









    A B C D E F
    6/18/2017 111-2222222-3333333 1179714 JA5939 AMZ bob@comic.com
    6/18/2017 222-3333333-4444444 1179723 NE1347 AMZ geoff@live.co.uk
    6/18/2017 333-4444444-5555555 1179727 JE1133 AMZ bobby@live.co.uk
    6/18/2017 444-5555555-6666666 1179722 DA5721 AMZ bill@hotmail.com
    6/18/2017 555-6666666-7777777 1179745 SA2294 AMZ wes@sky.com











    What I need is a function that takes the email address in column A of sheet1, finds it in sheet 2 (which is a separate workbook), then finds the data in columns C and D for that email address, and pulls it back into sheet one, into colums C and D.

    Because the data I want is to the left of the email address in sheet 2, I can't figure out how to pull the data from columns C and D at the same time. I can do them one at a time but not both together... is this possible?

    Here's what I have so far:

    {=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$C$1:$C$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))}

    That pulls the data from column C, then this for column D:

    {=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$D$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))}

    I've used INDEX and MATCH because as far as I can tell, VLOOKUP can't look left.

    I also tried looking for a function that reverses the order of the columns in the second workbook, but to no avail.

    If anyone can help me with this I'd be extremely grateful.

  2. #2
    Board Regular
    Join Date
    Sep 2012
    Location
    Melbourne Australia
    Posts
    1,534
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    do you want the results of C & D in the one cell?
    index match is the function you want. It is the vlookup in either direction.
    If you want C & D in the same cell you can consolidate the values
    {=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$C$1:$C$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))&" "&INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$D$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))}
    That might be what you're looking for
    Write a man a macro he is happy for the day....teach a man to write a macro, he'll be happy forever!

  3. #3
    New Member
    Join Date
    Jun 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    Quote Originally Posted by dermie_72 View Post
    do you want the results of C & D in the one cell?
    index match is the function you want. It is the vlookup in either direction.
    If you want C & D in the same cell you can consolidate the values
    {=INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$C$1:$C$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))&" "&INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$D$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0))}
    That might be what you're looking for
    Hi, firstly thank you for your help.

    I actually want the results of C & D in two cells though, and over two columns. Columns B & C.

    Hope you can help with this.

  4. #4
    Board Regular
    Join Date
    Nov 2014
    Location
    South Africa
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    Try this,
    B1
    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),3)
    C1
    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),4)
    Last edited by DHayes; Jun 19th, 2017 at 04:05 AM.

  5. #5
    New Member
    Join Date
    Jun 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    Quote Originally Posted by DHayes View Post
    Try this,
    B1
    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),3)
    C1
    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$D$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),4)
    I will try this. Can it be put in the formula bar all at once and applied to the 2 cells, or does it have to be done 1 column at a time.

    To confuse matters further. Ignoring the above tables for a second. I actually want to pull data from columns C+D from the external workbook, and put into columns D+E on the other sheet. If this is possible how would this code need to change?

    Thank you in advance for any help.

  6. #6
    Board Regular
    Join Date
    Nov 2014
    Location
    South Africa
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    Hi,
    It can be put in the formula bar. It needs to be put into the cell for each result. it can be copied down. The numbers in the red text indicate the column number. eg 3 = column C.

    in cell D1
    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$F$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),3)
    in Cell E1
    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$F$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),4)

    Hope this helps

  7. #7
    New Member
    Join Date
    Jun 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    Quote Originally Posted by DHayes View Post
    Hi,
    It can be put in the formula bar. It needs to be put into the cell for each result. it can be copied down. The numbers in the red text indicate the column number. eg 3 = column C.

    in cell D1
    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$F$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),3)
    in Cell E1
    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$1:$F$5,MATCH(A1,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$1:$F$5,0),4)

    Hope this helps
    What am I doing wrong because this still doesn't work. It just copies the formula into the cell.

    =INDEX([EbayAmazonWeeklyRepot.xlsx]Sheet1!$A$2:$F$2207,MATCH(C3,[EbayAmazonWeeklyRepot.xlsx]Sheet1!$F$2:$F$2207,0),3)

  8. #8
    Board Regular
    Join Date
    Nov 2014
    Location
    South Africa
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    What error are you getting?

  9. #9
    New Member
    Join Date
    Jun 2017
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    Quote Originally Posted by DHayes View Post
    What error are you getting?
    No error, just the formula appearing in the cell. D3

  10. #10
    Board Regular
    Join Date
    Nov 2014
    Location
    South Africa
    Posts
    185
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieve 2 columns of data to the left of lookup value on another sheet.

    check in your formula tab that "show formula is not selected"

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com