How To Merge Two Excel SpreadSheets Using Vlookup

http://www.workwithneilball.com/ Click this link to sign up for my email with more free tips and tricks and also for information on making money online

Merging two spreadsheets together in Microsoft Excel is not something that is as easy to do as it should be if you dont know how to do it. Nor is it obvious how to do it. In this video Neil Ball explains how to merge to spreadsheets into one using the Vlookup in Microsoft Excel


Excel Merge Two SpreadSheets Using Vlookup
Best Way to merge two spreadsheets
Easiest Way to merge two spreadsheets
excel
merge spreadsheets
combine excel spreadsheets
vloopup
iferror
excel tips
Microsoft Excel

How To Merge Two Excel SpreadSheets Using Vlookup

Work With Neil Ball
http://www.youtube.com/user/WorkWithNeilBall

How To Merge Two Excel SpreadSheets Using Vlookup

---

Closed Caption:

Hello it's Neil Ball, your online marketing
coach from www.workwithneilball.com.In this
training, I am going to demonstrate how to
merge two spreadsheets together. This example
is going to use two spreadsheets in the same
worksheet. The easiest way you have got two
separate books with the spreadsheets in is
probably to put them in the same spreadsheet,
or the same workbook. Now once you have got
the two spreadsheets in the same workbook
like I have here, so I have got sheet one
which has got the names, address, cities,
states, zip for 29 people.And sheet two has
got the names and the date of births. And
that is actually for 100 people. So obviously,
there is more data in sheet two than there
is in sheet one. So to do this, the process
of merging, it actually uses the VLookUp function
within excel. Now, I found that the explanation
within excel of how to use this function is
not very good. So a video is an excellent
way of demonstrating how to use it, and then
once you see it, you see it is a very easy
thing to do.So, the first thing I would recommend
you do, is to take the range of data that
you are wanting to use. That you want to merge.
So, I want to take data from here, so there
is a match and put it in the spreadsheet,
so what I am going to have here is, every
field that has a name that matches will also
have the address details populated over here,
for example the name column is common to both
spreadsheets and will actually be used to
tie the data across to each other. Very important,
you have got to have data that has got some
commonality so, the best way to do this is
to highlight all of the cells where the data
is that you are going to compare and copy.So,
I have highlighted all those there. So if
you then go up to formula on the top tab here,
and click define name. You can actually give
that a name, so I am going to call it NamesAndAddresses.
When you do this, I think it doesn't like
you having spaces in here, so you need to
keep everything in a format similar to that,
then click ok.The next thing that you need
to do is go into sheet one, and copy these
fields here. Fields that you are actually
going to want to copy across. So, name is
already in sheet two, so I don't need that.
So I am just going to copy across these headers
here, for address, city state and zip. So
I can go copy, and drop them into here with
paste. Now it is time to bring the data across.
Now the easiest way to do this as I said,
is use the VLookUp.So then click here, and
then click, equals. You will then see here
that this box changes. I am going to tell
you how to find the VLookUp just in case you
do not know how. One easy way of doing it,
is to go to more functions like that and then
type in VLookUp.Like that, and then click
go. You will then see at the top of this list
is VLookUp.So if you double click that. Then,
it brings up this box, to make this work,
what you need to do is complete these boxes.
You will see if you read this information
here, it is not very clear as to what it is
actually saying, or what you need to do. So
the first thing that you need to do is to
define what it is when excel is looking at
this particular row it is going to be looking
for in the other spreadsheet. Or in sheet
one, so, in this case it is going to be that
which is A2. So, that is the first thing.
The next thing you need to think about is,
when this is copied, to the next columns,
you are always going to want to use column
A for your point of reference, so to make
sure that it always uses column A for the
point of reference, you need to put a string
sign there, so $A. Now the next thing, Table
array now table array was what was highlighted
when I showed you all of this, and given the
name that we did before. So I will just get
rid of that there and go back to here.Like
that, and to bring up the list of names that
you have created, press F3, you will see that
it brings up the paste name in here, so if
you click Names And Addresses which is what
I named it, and click ok, it will then populate
that here like that. This column here, the
column index number, is the data column that
is going to populate this particular cell
from sheet one.So, and the way this works,
it numbers these, this is column one. The
name column here is column one. The address
column two. Column three is the city column.
Column four is the state and column five is
zip. So, as I said before, as I have already
got name in here, on sheet two we do not need
to copy name across again. So the first thing
we need to do is copy this column two. So
here, you can just put 2.It has actually just
made an error there because it was still on
there, but if I just get rid of that on the
end I should be alright. Now, the next thing
you need to look at, is Range lookup. Now
again, this is a bit of a confusing explanation
of what this actually means. Obviously written
by a programmer.other than somebody who reads
English. What it is actually saying is that
if you want an exact match, then put FALSE
here. If you are wanting to do this, then
that is what you are wanting to do.So, FALSE,
is what you want to put in there, and then
click ok. Now, it has come up with N/A. What
that is actually saying is that, that particular
name does not match any of the names in this
list here. So, what we need to do now is,
copy that down all of here. So if you just
position your mouse over this right hand corner
of the box and double click it. It will actually
copy that all the way down for every column,
sorry for every row that is populated here.So
as you can see, what it has done is, it has
identified all of the data where there is
a match, so this here, is obviously a match.
To there, and it has then copied the first
part of the address to this box here. Which
is what we said we wanted, we wanted it to
take column two. And put that here. So, that
is all ok. So, the next thing, which is a
little bit annoying is these N/A's here, they
really are annoying.Now, you can get rid of
that, by using the function, which is built
into excel. 2007 and also excel 2010. I am
not sure if it is in 2003 but it is in those
two.It is called the IfError function. Now,
just to demonstrate this for you first, if
I just put equals here and then, I can show
you where it is on here. You can actually
find it by putting IfError like that. And
you will find it and you will see returns.
Well half the time these are a little bit
confusing, but this is the function that you
use for this. Basically what you are saying
is, if there is an error, what does it do?
So you need to tell it what to do when there
is an error.So, if you click ok, then, the
first part is the value. So the value is,
when I actually set this up properly, is going
to be the VLookUp function. That we have set
up for this column here. And, then what you
need to say is, you need to say if there is
an error, what is it that you want to display.
Now in this particular example, we want it
to display a blank cell. So, to do that if
you put a double speech SPACE double speech,
like that and then click ok. That is how you
do it, so, for the sake of argument, I am
going to write Test there. It allows you to
see how it actually structures this. So, what
we need to do is put this here, as you can
see in the box up here. Where it says IFERROR(
Test,"" ).What we need to do, is use that
formula there, where it says Test, we need
to put the VLookUp information. That is in
column A2 here. So, I will show you how to
do that now. So, I am going to leave that
there just in case. But, if I come across
here, the way that you can do that is by placing
your cursor just after the equals there. And
now you know the format of it, you can actually
write Iferror like that. And then the open
brackets then VLOOKUP is replacing where there
was a test, you are going to put where test
was in the example that I have just showed
you. Then comma, then double speech marks,
then SPACE then double speech marks, close
brackets.And now press enter, you will see
the error has now disappeared. So, we now
copy that down the column by double clicking
the bottom right. It is looking a lot neater
now because all of the errors have gone out
of it. The next thing is, how do we get the
city here. That is very straight forward,
all you need to do is copy this cell to here.
And amend it slightly. So, we have copied
it to there, the only thing that this needs
is column two is not what we want in here.
We actually want column three. So, that needs
to be changed. So coming back to here, edit
that manually. Just change the two to a three.
And again that can now be copied all the way
down and you can see that it is now populating.
And the same again, copy to the next one,
like that, I am just dragging that copy it.
And once that comes in there, we now want
state, which is column four. So change number
three here, to number four.And then copy it
down there, like that. And the next column
I assume was going to be column five. Indeed
it is. So there is column five. So now, come
back to here, and copy this cell to here,
like that, and then edit the four, so it is
now five.Press return and then copy that all
the way down, by double clicking the bottom
right hand corner of this box, we have now
got the zips all the way down, like that.And
that is the two spread sheets merged together.Which
once you actually understand it, it is a great
feature, and it took me quite a while to work
out how to do this. So, hopefully that has
been helpful for you. To get more tips, tricks
and information, and also information on making
money on line, you can join my mailing list
by going to www.workwithneilball.com.Again,
that is www.workwithneilball.com.Until next
time, goodbye.