Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    search & populate a workbook

    Greetings,

    Iím using Excel 2010 and try to create a formula to search for a value from one workbook in another and populate the blank columns.

    In wb1 sheet1 the cell content of column D is populated, but columns G & H are not. My goal is to search for column D cell content within wb2, sheet2 in column P. If the record exists in column P, then take the corresponding record from columns V & W from wb2, sheet2 and copy the cell values in wb1 columns G & H.

    wb1>sheet1>Column D (populated) = wb2>sheet2>column P
    wb1> sheet1>column G=wb2>sheet2>column V
    wb1> sheet1>column H=wb2>sheet2>column W

    wb1 has about 100 rows & my target workbook is (wb2) is populated and has about 7500 rows

    Thank you in advance,
    ocm


  2. #2
    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
    I presume there are headers in Row1
    In wb1> sheet1>G2 enter:
    =VLOOKUP(D2,[wb2.xlsx]Sheet2!$P$2:$V$7500,7,0)

    In wb1> sheet1>H2 enter:
    =VLOOKUP(D2,[wb2.xlsx]Sheet2!$P$2:$W$7500,8,0)

    Copy G2:H2 and paste it to G3:H100

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply Steve.

    Overall, the Vlookup works fine. However, in my wb1 column D I have a value called ďSTC.TERMĒ & this value is also in wb2 > Column P (my search target). I applied sort on wb2 column P & verified this value exists (spell the same way, no space etc...)
    For some reason, I got #N/A in wb1.

    Can this be accomplished using IF then else etc? I was trying to compare it w/ VLookup & see if I get the same result.


    Regards,


    OCM

  4. #4
    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
    Do simple comparison of the 2 cells first to make sure they are equal (I suspect they are not). The key is finding out the difference. If you can't find it, send a copy that demonstrates this and I will take a look.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Steve,

    I found the problem as this is related to space in one value & not the other. I used TRIM function in my VLOOKUP to remove the unnecessary spaces and works fine now.

    Regards,

    OCM

Posting Permissions

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