search & populate a workbook

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote