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

Danny Rocks
The Company Rocks

[hadesm]

This was truly amazing. This saved me hours of work on a spreadsheet that contained over 65, 000 records. Thank you.

[emirhantekin]

If you don't want to manually change the numbers from 3 to 4, 4 to 5 you can use this in the formula.
ROWS($E4:E6)

[mwsd]

Thank you very much.
This is perhaps the best Excel tutorial I've ever seen on Youtube.

[isgrad]

How would I do this when I dont have a unique identifier on my 2nd table ?

[andrescastillo]

summary:
start with two workbooks with their own sheets.
Click on the tab for the sheet you want to merge into the other workbook.
Home>Cells>Format>Move or copy sheet>withing the dialog box, choose desired workbook, move to end, and make a copy
In a new cell of the desired sheet, use:
=VLOOKUP($reference to unique id cell of the same desired sheet, named range in second sheet, column index within the range of second sheet that unique values lie, FALSE to find exact match)
Can wrap the VLOOKUP function into an IFERROR function by passing it in as the first argument, and the second argument being a custom message.


Practical use scenario:
I used this as a way to compare:
a list of people who signed into a sign-in sheet for a meeting.
against:
a list of people who were supposed to attend the meeting.
The Unique ID was the emails, converted to =lower(), then copy and paste the values to overwrite the original emails.


This allowed me to have a follow-up list of emails for so I could reach out to people who did not attend.
The people who did not attend had no values.
So, I filtered on these so I can get my follow-up list.

[Elcchin]

Not sure if you're still answering questions but, I'm using vlookup to find a specific group of MAC addresses from a bigger list.  These are two separate spreadsheets in the same workbook.  I'm using XL2010.

The formula is basically:
=vlookup(cell to start looking for specific group of MAC addresses, table in other spreadsheet tab, column with bigger list of MAC address, false) or =vlookup(A2, tab$A$2:$C$3150, 3, FALSE)

However I get #N/A.  But when I validate this using the find (ctrl-f) and imputing the MAC address of the specific to the bigger list I find a few.  The number of specific MAC = approx 100.

When I click on the 'YEILD' to "Show Calculation Steps..." I get to the first variable and then "The cell currently being evaluated contains a constant." and it only allows me to "step out".

I read somewhere that XL2010 has a problem with vlookup function looking at tables in a separate workbook.  However, this isn't not a different workbook it's a spreadsheet in the same workbook.

Any assistance would be greatly appreciated.

I have watched your video about 3 times and am doing everything according to your tutorial.  But I may have a block of some sort that isn't allowing me to see my mistake.

Thanks

[alyssalingolive]

Thank you so much for sharing this. Very thorough, straightforward and easy to follow. I think it's going to be a little upward learning curve, but it would have been impossible without your tutorial.

[bernatbosch]

Let me make a suggestion to improve the procedure. The best would be to substitute the action of changing each time and for each column the value of the third argument within the lookup function (3, 4, 5...) by a reference to the column number of the first range by using the actual columns of the destiny range (row()-4).

That is to say, substitute the manual reference to the column number in the argument number three of the lookup function by a more flexible reference that would vary in accordance to the column in which data will be placed in the destiny range.

[manikhurana]

It is not working. Can you please help me. Its result comes N/A always

[lbsackett]

Excellent explanation. You just saved me hours of work! Thank you.

[carolineleonard]

Hi Danny thanks for this, but what happens if I want to do this and delete the second sheet afterwards, will I not lose all the data?

[paulinekoh]

thanks! this step by step video is AWESOME & USEFUL. 

[tamaramitchell]

I'm really hoping this works with Pivot Tables!

[evelynbravo-sanchez]

OMG what a great teacher you are thank you!!!

[krismaly]

I enjoyed watching this video.

Thanks for educating the community and appreciate your volunteership.

Is it a must to keep it open both work books? Is there any ways that go to folders/files to choose a workbook?

Thanks a bunch

[DannyRocksExcels]

Thanks for adding your feedback.

As we both know, there are several ways to accomplish any end result in Excel. I agree that there is a difference between a cell that contains a NULL value - e.g. "" and a cell that contains a Blank - e.g. " "

The way that I learned to show a blank text string was "space". I still like to use this approach when I teach. Once you have mastered one way to do things in Excel, you are free to experiment with alternatives.

Danny Rocks
The Company Rocks

[DannyRocksExcels]

Hi Scott - There are several ways to create named ranges. For example, if you are using Excel 2007 or Excel 2010, you can do this in the Name Manager dialog box. This is on the Formulas Tab of the Ribbon.

When you look at the "Refers to..." you can see the Sheet Name and selected cells used for each Named Range.

Danny Rocks
The Company Rocks

[thomasbonello]

This doesn't solve the problem of having data that doesn't exist in both of the tabs, AND VICEVERSA, f.i. Data1exists  in Tab1 but NOT in Tab 2: cool, will display #N/A, BUT if we have Data 2, exists in Tab 2 but NOT in Tab1, this data will be skipped and therefore we don't have a real consolidated vision of the data... 

[stormymongiello]

I don't use VLOOKUP very often so this is my go to video Thanks

[Siberian_valenok]

Why don't you simply copy and paste the entire data from the second file into the first one? Why create two sheets and do this lookup jazz?