How to search for a common condition within two different workbooks then copy cell.

%3CLINGO-SUB%20id%3D%22lingo-sub-2578304%22%20slang%3D%22en-US%22%3EHow%20to%20search%20for%20a%20common%20condition%20within%20two%20different%20workbooks%20then%20copy%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2578304%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20create%20a%20query%20or%20formula%20that%20will%20check%20for%20matching%20data%20between%20two%20different%20workbooks%20and%20then%20copy%20data%20to%20one%20of%20the%20workbooks%20when%20a%20match%20is%20found.%20Ex.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorkbook%201%20has%20the%20following%20cells%3A%20Column%20A%20%3D%20VIN%2C%20B%20%3D%20Auto%20Make%2C%20C%20%3D%20Model%3C%2FP%3E%3CP%3EWorkbook%202%20has%20the%20following%20cells%3A%20Column%20A%20%3D%20VIN%2C%20B%20%3D%20Auto%20Make%2C%20C%20%3D%20Tag%20Number%3C%2FP%3E%3CP%3EI%20would%20like%20to%20create%20a%20query%20or%20formula%20that%20would%20compare%20column%20A%20between%20the%20two%20workbooks%20and%20then%20where%20a%20match%20is%20found%20copy%20Workbook%202%2C%20Column%20C%20to%20workbook%201%20Column%20D.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2578304%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2578405%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20search%20for%20a%20common%20condition%20within%20two%20different%20workbooks%20then%20copy%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2578405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1110697%22%20target%3D%22_blank%22%3E%40Bfryar%3C%2FA%3E%26nbsp%3BThere%20are%20plenty%20ways%20to%20achieve%20this.%20XLOOKUP%2C%20INDEX%2FMATCH%20or%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20favour%20Power%20Query%20for%20these%20type%20of%20tasks%20(especially%20when%20the%20data%20sets%20become%20large%20and%20complex)%20as%20it%20leaves%20the%20raw%20data%20untouched%20and%20you%20avoid%20having%20to%20write%20moderately%20complex%20formulae.%20But%20that's%20just%20a%20personal%20preference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20workbook%20contains%20all%20three.%20Depending%20on%20your%20Excel%20version%2C%20the%20XLOOKUP%20option%20might%20not%20work.%20And%20if%20you%20are%20on%20a%20Mac%2C%20you%20may%20skip%20the%20Power%20Query%20option.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I want to create a query or formula that will check for matching data between two different workbooks and then copy data to one of the workbooks when a match is found. Ex.

 

Workbook 1 has the following cells: Column A = VIN, B = Auto Make, C = Model

Workbook 2 has the following cells: Column A = VIN, B = Auto Make, C = Tag Number

I would like to create a query or formula that would compare column A between the two workbooks and then where a match is found copy Workbook 2, Column C to workbook 1 Column D.

1 Reply

@Bfryar There are plenty ways to achieve this. XLOOKUP, INDEX/MATCH or Power Query.

 

I would favour Power Query for these type of tasks (especially when the data sets become large and complex) as it leaves the raw data untouched and you avoid having to write moderately complex formulae. But that's just a personal preference.

 

The attached workbook contains all three. Depending on your Excel version, the XLOOKUP option might not work. And if you are on a Mac, you may skip the Power Query option.