Map different cells into a row based on other cells

EuginG

New Member
Joined
May 3, 2016
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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).
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
You can use simple vlookup in first file. You have Client Unique Id which can be used as lookup value.
 

EuginG

New Member
Joined
May 3, 2016
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=VLOOKUP($A1,Sheet2!$A$1:$D$6, COLUMNS($A1:B1), FALSE)
 

EuginG

New Member
Joined
May 3, 2016
Messages
14
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
Thanks also for updating your profile. (y)
 

EuginG

New Member
Joined
May 3, 2016
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Glad we could help & thanks for the feedback.
Thanks also for updating your profile. (y)
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top