Solved Excel, match columns, import data if match

September 5, 2011 at 12:01:35
Specs: Windows XP
I have two different files..

Comparing them and if they match, I need to import four more columns from File A to File B.

For example

100 Light Street is in Column A on Sheet 1
100 Light Street is in Column C on Sheet 2

If its a match, I need Excel to bring over Column D, E, F, G from sheet 2 and put them in Columns, B, C, D, E in Sheet 1..

If no match, I dont need anything brought over.


Can anyone help me please? Need this done by 5 pm tomorrow.. Was hoping to solve it today.

Thanks
Steve


See More: Excel, match columns, import data if match

Report •

#1
September 5, 2011 at 13:00:34
VLOOKUP sounds like it will work.

Have you read up on VLOOKUP in the Excel Help files?

That said, there is something a bit confusing about your post.

The first part of your post mentions 2 files, File A and File B, but your example uses sheets, i.e. Sheet 1 and Sheet 2.

Do you have 2 files or 2 sheets or both? Do you mean that data from File A Sheet 2 should be imported to File B Sheet 1?

In any case, VLOOKUP sounds like the way to go.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 5, 2011 at 14:55:28
Let me reclarify.

I have two files, A and B...

100 Light Street is in Column A on Sheet 1 of File A
100 Light Street is in Column C on Sheet 1 of File B

If they match, I need to Column D, E, F, and G from Sheet 1 of File B to go to Columns B, C, D, E on Sheet 1 of File A.

I can do the Vlookup to see which ones match, just having trouble getting the data in Columns B, C, D, E and to transfer over, which is address, city, state, zip..

I have over 785 matches which 2600 lines it is looking at.. Which is why I was looking for an easy code to do this for me


Report •

#3
September 5, 2011 at 16:21:29
✔ Best Answer
re: "Which is why I was looking for an easy code to do this for me"

Well, the "easy" code that you want to do something for you has to be written by someone else. You get the easy part of simply running the code. Hardly seems fair, now does it?

I'm not sure how you are getting VLOOKUP to "see which ones match" but can't get the data transferred over.

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

The col_index_num argument is where you tell VLOOKUP which column to return.

With 100 Light Street in [File A.xls]Sheet1'!A1, put this formula in [File A.xls]Sheet1'!B1

=VLOOKUP(A1, '[File B.xls]Sheet1'!$C$1:$G$2600, 2, 0)

Since the first column of the table_array is C, the col_index_num of 2 (referring to the 2nd column of the table_array) will return the value in Column D.

=VLOOKUP(A1, '[File B.xls]Sheet1'!$C$1:$G$2600, 3, 0) will return the value in Column E, etc.

If you don't want to see #N/A when the value isn't found, try this. It will return an empty cell if the lookup_value isn't found.

=IF(ISNA(VLOOKUP(A1, '[File B.xls]Sheet1'!$C$1:$G$2600, 2, 0)), "", VLOOKUP(A1, '[File B.xls]Sheet1'!$C$1:$G$2600, 2, 0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 5, 2011 at 17:41:39
Thank you.. It seems fair to me, I seek out guidance, and you delivered. I appreciate your kindness.. Thank you..

Report •

Ask Question