Excel question: merging two spreadsheets

I don’t know if this is possible, however…

Spreadsheet A has 3 columns and 1000 rows. The columns are Last Name, First Name, and Phone Number.

Spreadsheet B has 3 columns and 500 rows. The columns are Last Name, First Name, and Email Address.

Each of the 500 entries in Spreadsheet B can be found in Spreadsheet A.

Is there an automated method to add the email addresses from B into A?


Add a new column to each sheet which concatenates the First Name and Last Name together. Then use a VLOOKUP function to get the email address for each row in spreadsheet A.

Do what to the who now? :slight_smile:

I really appreciate the reply, hammos, but I’m afraid I need much more guidance than that.

How do you combine first and last names into a new column, for example?

Here’s an example from the internet:


Index Match
={index(‘spreadsheet B’!EMAILCOLUMN:EMAILCOLUMN,Match('spreadsheetA’Lastname&firstname2,‘spreadsheet B’!LastnameCOLUMN&firstnameCOLUMN,0))}

To wrap in {} you have to put the cursor at the end of the )) and hit Shift, Ctrl, and Enter

This is all Martian to me.

I’m going to find a good Excel tutorial and work from the very beginning.

If your spreadsheets are different Excel files, this might be a good place to start, particularly the section on What an external reference to another workbook looks like.

If the spreadsheets are simply different worksheets within the same Excel file, read here.

Let’s say cell A1 is “Lastname” and cell B1 is “Firstname” cell C1 is 'Phone Number"

You can combine the names with this formula :


So, in cell D1, you put that formula, and it will come out “LastnameFirstname” copy it down for the whole set of data in BOTH spreadsheets, you have step 1 complete.

Step 2 is to setup the data from Spreadsheet B. Highlight, copy and paste all the data from Spreadsheet B into a second tab of Spreadsheet A this will simplify the lookup you’re going to do. Call this tab Sheet2 (it may already be called this) To make it even easier, in the data from spreadsheet B, copy the email addresses from column C to column E, one column to the right of the LastnameFirstname.

Last Step, back to Spreadsheet A. In cell E1, you’re going to do a vertical lookup,


This lookup with check cell C1, the LastnameFirstname you’re trying to match, will go to Sheet2 and look at the entire columns of C and D, where you have the second spreadsheet’s LastnameFirstname you’re trying to match, and the email you’re trying to find. Then if it finds a match, it goes to the 2nd column (D) and displays that value in the cell E1, where you want it.

mmm, I liked the sound of Meatros’ solution but I couldn’t get it to work. But based on it, I came up with a formula you can use. (This was a learning experience for me… usually I do what Cheesesteak just described.)

Assumptions based on your OP: Your Excel workbook comprises at least two worksheets, including ones named Spreadsheet A and Spreadsheet B. Both use Row 1 for column headings. Spreadsheet A uses Rows 2-1001 and Columns A, B, and C for the data you described; similarly, Spreadsheet B uses Rows 2-501 and Columns A, B, and C for the data you described.

If so, paste the following into Spreadsheet A, Cell D2:

=INDEX(‘Spreadsheet B’!$C$2:$C$501,MATCH(1,(A2=‘Spreadsheet B’!$A$2:$A$501)*(B2=‘Spreadsheet B’!$B$2:$B$501),0))

Like Meatros’ described, this is an array formula, so it won’t work unless you enter it as an array formula… when you paste it in or otherwise edit this cell, finish editing by hitting SHIFT-CTRL-ENTER. When you do, the formula will appear with brackets around it:

{=INDEX(‘Spreadsheet B’!$C$2:$C$501,MATCH(1,(A2=‘Spreadsheet B’!$A$2:$A$501)*(B2=‘Spreadsheet B’!$B$2:$B$501),0))}

Copy Cell D2 into cells D3 through D1001. Now Column D should contain email addresses for the 500 people you have them for, and error messages for the other 500. There is probably a way to edit the formula to output something nicer than an error message for the latter, but hopefully this will get you off to a good start.

I was doing it on the fly so I probably missed something.

I like that you don’t reference the entire column, but I’ve found that doesn’t really save any resources when running a large amount of formulas - or maybe it doesn’t save enough to matter.
I like excel - I run a lot of reports, dashboards, etc. It’s a handy tool.

My favorite thing I figured out was using indirect. So I have a large spreadsheet full of various tabs. I have one master tab where I reference all the other tabs. Well, I don’t want to keep creating formulas that point to the individual tabs, that’s a pain. So I pick one cell - say B78, in that cell I change it to text and then type in whatever tab I’m interested in, the rest of the formula updates.

So for a countif:

This part: “’”&$D$78&"’! refers to whatever I typed into B78 (which is the name of a tab).

I was stoked when I figured that out. :slight_smile: