SOLVED

Match Column A of One Excel File with Column A of another Excel File and copy Data

Copper Contributor

Hello Community,

Please help me out with this.

I have two Excel files. I want to match Column A of one file with Column A of another file. If both are same, I want to copy data in Columns B,C,D,E of the first file to the other file.

I am not good at macros. If you are suggesting macros, please add a note as to how to deploy the macros.

8 Replies

@ethinix 

 

Hi,

 

I think you can just use CHOOSEROWS function to copy entire row from one file to another.

 

Screenshot 2023-11-11 at 1.27.43 PM.png

I attached an example. Book1.xlsx is where the original data sits. Book2 is the file you want to copy data to. 

 

best response confirmed by ethinix (Copper Contributor)
Solution

Hi @ethinix 

 

Assuming your 2nd file has a header (as below) in row 1 and the values you want to match (in your 1st file) start in row 2:

 

Sample.png


in B2 of your 2nd file:

=XLOOKUP(A2, 'File 1'!A:A, 'File 1'!B:E, "No match")


Sample attached ('File 1' and 'File 2' are in the same workbook for simplicity)

@L z. 

Thanks for the quick response. Here's what I want to achieve.

Excel.jpg

 

Thanks for the quick response. Please check the image I posted below. That's what I want to achieve.

@ethinix 

Hi, I think @L z. 's XLOOKUP formula still works:

I attached an example using your data:

 

Yes. It works. Thank you @L z
Thanks so much Rachel.
Glad this helped & Thanks for providing feedback
Thanks @rachel too
1 best response

Accepted Solutions
best response confirmed by ethinix (Copper Contributor)
Solution

Hi @ethinix 

 

Assuming your 2nd file has a header (as below) in row 1 and the values you want to match (in your 1st file) start in row 2:

 

Sample.png


in B2 of your 2nd file:

=XLOOKUP(A2, 'File 1'!A:A, 'File 1'!B:E, "No match")


Sample attached ('File 1' and 'File 2' are in the same workbook for simplicity)

View solution in original post