Map different cells into a row based on other cells

[EuginG]

Hi!

Is there a way to add rows with a data from one file to other based on cell similarities? Using Excel UI tools, VBA or formulas, whatever.

For example, in the first file there is a complete database of clients, e.g. 1000 rows with records divided into 3 columns. Column A contains clients unique IDs, B column – clients names, C – number of deals.

In the second file there are 250 rows of records and four columns. A and B columns are random clients IDs and names from the same database as in the first file, but C and D columns contain new data I need to merge somehow to the first file’s same clients respectively.

The question. How can I map those nonsequential 250 rows with new data respectively to the same IDs rows between those 1000 records (not overwriting anything)? Thus, I should get the first file with its A,B,C + new D and E columns (which were previously C and D columns in the second file).

 

[CA_Punit]

You can use simple vlookup in first file. You have Client Unique Id which can be used as lookup value.

 

[EuginG]

You can use simple vlookup in first file. You have Client Unique Id which can be used as lookup value.

Yes, VLOOKUP was a good idea. I figured out how to use it. Thanks! My formula was pretty simple:

=VLOOKUP(A1;Sheet2!$A$1:$C$6; 2; FALSE)

Where A1 is data form the first file I need to search. And Sheet2!$A$1:$C$6 - a scope of data from the second file (copied to a new sheet in the first file) where to search.
The only issue is that I cannot retrieve multiple columns. Only one at a time. E.g. many videos show that you can use an array "{}" to retrieve 2nd and 3rd column.

=VLOOKUP(A1;Sheet2!$A$1:$C$6; {2,3}; FALSE)

but in my Excel 2016 (Windows) it does not work. I tried many combination and solutions. None of them are workable. But if I try more than two indexes - I get error, because the array {2,3,4} Excel sees as {2,34}. If you know what is the problem, please advice.

 

[Fluff]

Please update your account details to show your version of Excel, as it affects what functions you can use.

With 2016 you will either need to copy the formula across & change the return column, or if you just want all columns in order you can use something like

=VLOOKUP($A1,Sheet2!$A$1:$D$6, COLUMNS($A1:B1), FALSE)

 

[EuginG]

Please update your account details to show your version of Excel, as it affects what functions you can use.

With 2016 you will either need to copy the formula across & change the return column, or if you just want all columns in order you can use something like

=VLOOKUP($A1,Sheet2!$A$1:$D$6, COLUMNS($A1:B1), FALSE)

I do not know how, but it works! When I entered the formula I was able to drag it on several cells forward (to cover necessary columns). And then dragged it down for as many rows as I need. And I see that the data was retrieved correctly. Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.
Thanks also for updating your profile.

 

[EuginG]

Glad we could help & thanks for the feedback.
Thanks also for updating your profile.

Thank you! The only thing I forgot to mention is that I needed to change commas to semicolons. Because Excel 2016 for Windows works like that. Maybe someone will help it too.

=VLOOKUP($A1; Sheet2!$A$1:$D$8; COLUMNS($A1:B1); FALSE)