How to Combine 2 Excel Workbooks Using VLOOKUP

This is a request from one of my viewers. In his organization, two people were working on this project and he needed to produce a consolidated Excel worksheet.

Fortunately, when I look at the workbooks he sent me, I noticed that both had a MemberID field that contained the Unique Account Numbers. With this knowledge, I decided that the VLOOKUP Function would be the easiest way to complete this task.

Here is a list of the Excel Techniques that I demonstrate in this tutorial:
* Move or Copy a worksheet to another Excel Workbook
* Create a Named Range to use as the "Table_array" in the VLOOKUP Function
* Use "Mixed Cell References" - e.g. $A4 - in VLOOKUP Function
* Use FALSE as the optional 4th Argument in VLOOKUP to produce an "Exact Match"
* Use IFERROR Function to prevent Error Messages from appearing

I invite you to visit my secure online shopping website - http://shop.thecompanyrocks.com - to examine the many training resources that I offer for sale.

Danny Rocks
The Company Rocks

---

Closed Caption:

hello and welcome back to tips and time
savers
i'm danny rocks in today's lesson I'm
going to respond to a viewer who asked
for my help and combining to excel
workbooks let's take a look at both of
the workbooks
here's workbook number one notice that
there are 26 records and the first field
contains a unique identifier the member
ID when i take a look at the second
workbook notice that there are fewer
than 26 records however we're fortunate
in that column a contains again the
member ID as the unique identifier so
knowing that I have both work sheets
contain a common field member ID
my approach and combining them is to use
the vlookup function are now
first order of business is what i want
to do when I look at the second workbook
it's going to be a lot easier if I make
this a worksheet inside workbook number
one
so easiest way to do that is to select
the worksheet and then or the label for
the worksheet and then either right
mouse click or in this case I'm going to
use one of the menu commands Home tab of
the ribbon cells group in the drop down
next to format i want to select move or
copy the sheet
so in this case i want to keep workbook
number two I don't want to delete it so
i want to create a copy but I want to
move it to another open workbook I want
to move it over here to this workbook
and I want to move it as the second
worksheet in this workbook
click ok so now you can see that in
workbook number one
I have both of the worksheets I i want
to be able to take the information from
the third column the fourth column the
fifth column 6 and the 7 in other words
the information from website city state
country and phone fields so i'm going to
select the labels i use ctrl c to make a
copy
come over here into my master works you
control V to make
a copy of them all right I'm going to
use the vlookup function the vlookup
function has three arguments my first
argument is I'm going to look for a
match to this sell the membership ID
where am I going to look for i'm going
to look for it in an array the array is
going to be this group of records now
rather than referring to range a fourth
through g18 all the time
I'm gonna use a named range so i'm going
to select the range from my array and
i'm going to give it a name list
- so i clicked up here in the name box
and i created a name that begins with
the letter which is important and
contains no spaces
my third argument and vlookup is to say
in which column is the information so in
this case counting from the left
website is the third column are we are
ready to use the lookup equals we look
up and I use tab and i use control a to
bring up the function arguments dialog
box
so my lookup value is going to be over
here in column a in this room now since
i'm going to be copying this formula
across what I want to do is I want to
freeze in place the reference to column
a so while the row will change i always
want to refer to column a the lookup
array remember i created that name range
up here so if i have an a range i can
use the f3 key board shortcut to bring
up the pace names dialog box
so like that name range which is the
array and remember for the column index
the website was the third column
counting over from the left in the
second worksheet
i'm going to use the fourth argument
because i want an exact match
I want to match on the membership ID not
an approximate match
so i'm going to type in false and now
i'm good to go
click OK and there you go I used vlookup
to look for this value
now remember i put a dollar sign in
front of
Colome because as I copy this across i
always want to refer to column a
where did I look I looked in that name
range list too
where was the information which column
county from the left
the website was in the third column in
the array and i use false because i want
an exact match and not an approximate
match
so now i can copy this down double click
and autofill oh why do we have this ugly
and a message well let's take a look
here is member ID 105 it exists in the
first list but remember one of five does
not exist over here in the second list
that's why we have this ugly and a our
message is there a way to remove that
yes there is
fortunately if you're using Excel 2007
or Excel 2010 there's an wonderful new
function that was at it called if our
let's see how this works
i use control a to open up the function
arguments dialog box two arguments for
the value the value is going to be the
vlookup function or i should say the
result of the vlookup function and if
there is an error
we want to display a blank so the way we
do that is double quotation marks space
double quotation mark
now let's apply this so let's come over
here to the first cell that uses the
lookup and we want to include the if our
function and then the vlookup function
as the first argument so after the equal
sign type if our left parenthesis
here's our first argument use a comma to
separate the arguments for our second
argument double quotation marks space
double quotation mark and of course
remember to put including or matching
right parenthesis so now when i click
this down you'll see that those na our
messages go away
all right remember that i use the dollar
sign to freeze the reference to column a
so if i copy this over to pick up the
city the city is going to be in the
fourth column so if I just make a copy
over here
I want to change one argument i want to
change 324
the city is in the fourth column again i
can copy that down really really simple
and I'll do something similar over here
i'll change the four as the column 25 so
the information for the state is in the
fifth column and again i can just
double-click and have that copied all
the way down
notice that there are no and a message
so again it's really very simple copy
this over and change the five is the
column index the six double click and
finally copy this over and make one
change
i'm going to change the six to seven and
again i'm going to double-click and
there you go
really really really very very simple
now notice over here I have some zeros
and things like that not that i don't
have the formatting that I had in the
original the way that I got the
formatting is I use the special
formatting so I selected the records for
the phone field i'm going to click on
the more button over here and what I
want to do on the number tabs come down
here and click special and I want to use
this mask with the phone number so now
click OK
so you see that I didn't have to add in
the parentheses for the area code and
the dashes for the phone number it was
done with formatting so really really
very simple
vlookup is a it's an essential function
- to learn if you're going to be master
of excel and i use the new if our
function so that any error messages that
would display because I didn't have a
matching record now display the blank
so the FR you look up and then i use
double quotation marks space double
quotation one so there you go
now you've learned how easy it is to
combine two lists we use vlookup because
we had a unique identifier member ID in
both lists and I look for you in
the next lesson