Automating record search

[Danny]

Greetings,

Following is what I would like to automate:

Take a record from Workbook #1 column D and search if this record exists in Workbook #2 Column P.

If the record exists, then take the corresponding record from Column W in the same Workbook (workbook #2) and copy the record in Workbook #1 column H. If no match, then column H will be filled with n/a.

Thank you in advance,
D.

 

[NBVC]

Try using a Vlookup formula.

e.g with both workbooks open, in H2 of Workbook1

=Vlookup(D2,'[Workbook2.xls]Sheet1'!P:W,8,FALSE)

Where D2 is first item in column D of Workbook 1 you want to search for in Workbook 2, Sheet1, column P and return result from corresponding row in column W of workbook 2 to H2 of Workbook 1.

Change workbook name and sheet name to suit.

You can copy the formula down the column H.

 

[Danny]

NBVC, thanks for your reply.

My goal is to populate columns G and H in workbook1, from Workbook2.
In workbook2 these columns are named different (I attempted to attach sample Excelworkbook to make it easier w/ no luck)

I tried the following:

=VLOOKUP(D3,'[Workbook2.xlsx]Sheet1'!$P$2:$W$400,8,FALSE)

=Vlookup(D3,'[Workbook2.xlsx]Sheet1'!P:W,8,FALSE)

and I was getting #N/A , and when the record exists, for some reason, I get 0

Thanks,
D.

 

[NBVC]

So you are sure you are trying to match D3 from your active workbook with column P of a workbook named Workbook2 and in a sheet named Sheet1 in that workbook? And you are definitely wanting to pull from column W?

If you get N/a, then there is not an exact match. Something is different. It could be format if one is number formatted and the other is text formatted (if you are comparing numbers). If you get 0, it might mean that column W has a blank where the workbook1 column D matches Workbook2, column P.

To attach workbook, either save as .xls file or zip the file. This forum doesn't accept .xlsx files.

 

[Danny]

Thanks NBVC,

I’m sure that the value in column D, wb1 exists in column P, wb2. Not all of them but, most of them.

I'm glad you mentioned about formatting: column D and other columns like P are a mixture of TEXT & NUMBERS. Ex. Address1, Phone Number1 etc.

I hope the attached sample screenshot helps…

Regards,

D.

 

[NBVC]

Nobody can test your excel formulas on a word document. Please post Excel sample workbooks only! (i.e. as .xls or zipped).

None of the items in column D of Workbook1 appear in Workbooks2 column P... as per your sample... so..... what are you expecting here?

 

[Danny]

Thank you, I've attached the strip version of the excel wb.


Regards,
D.

 

[NBVC]

So, I am still not sure of the problem.

None of the items in column D of Workbook1 are found in column P of Workbook2.... that is why you get #N/A error. Are there any that are supposed to match in those samples?

 

[Danny]

Thank you NBVC for your reply.

I was able to sort out my problem: I wasn’t sure why 0 was returned for cell H and was because there was a match in P but, corresponding cell W is empty
You were right the result = #N/A, when there is no match at all.

Thanks again.
D.

 

[NBVC]

Thank you NBVC for your reply.

I was able to sort out my problem: I wasn’t sure why 0 was returned for cell H and was because there was a match in P but, corresponding cell W is empty
You were right the result = #N/A, when there is no match at all.

Thanks again.
D.

As I mentioned in Post #4 above.