# Merging Two Excel Spreadsheets Based On Common Field

Despite the inconsistency in the number of fields, the compilation is still possible as long as the combination is done using fields that exist in all files the field names that are common. Within Excel, go to the Data ribbon and click on “Get Data”, “From File” and then on “From Folder”. Paste the previously copied path or select it via the “Browse” function. Continue with “OK”. If all files are shown in the following window, either click on “Combine” (and then on “Combine & Load To”) or on “Edit”. Finally, on line 59, we write each header column name to its appropriate column number on wksDst, our Destination Worksheet — a cinch, since dicFinalheaders(varColHeader) gives us the column number. That wraps up Phase 1 and sets us up for an easy Phase 2 — take a moment to celebrate and enjoy this gif of Charlie shooting a gun. Unfortunately, Excel doesn't provide an easy way to merge workbooks or worksheets based on a key. We could have exported the data into Access and merged them that way, but there's an easier way to do it right in Excel using VLOOKUP or OFFSET and MATCH functions. Click Kutools Plus Tables Merge to enable the tool. In the Tables Merge dialog, you need to do as below: Step 1: choose the main table and lookup table ranges. Step 2: click Next to go to the step 2 of the Tables Merge dialog, select the column you want to look up based on.

Users of UW-Madison's institutional Tableau workbooks may need to pull data from one Microsoft Excel spreadsheet into another spreadsheet. This KB article explains how, by using an Excel formula called vLookup.

**How does the vLookup formula work?**

### Merge Excel Spreadsheets Using Vlookup

- Locate where you want the data to go. Click that cell only once.
- At the top, go to the
**Formulas tab**and click**Lookup & Reference**. - Select vLookup
- Excel’s vLookup wizard will pop up. We’ll walk through each part of the formula.
**Lookup_value**Find the Unique Identifier (lookup value). It is usually in the same row as the empty cell you selected.

Click once on the Unique Identifier so that the cell position will automatically fill in. In this example it is cell B2.- Go to the next field,
**Table_array**(click in it once). In Spreadsheet 2 highlight the table containing the info you want, starting with the Unique ID.

In this example, Excel looks up Campus ID 555123123 in the first highlighted column of Spreadsheet 2.

Note: Make sure each Unique ID is listed only once in the table_array (on the second spreadsheet) so that vLookup retrieves the correct value. For example, if 555123123 is duplicated in the table_array, where Student [email protected] is the email in one row and Student [email protected] in the other, Excel will choose one of the emails for you. - Go to
**Col_index_num**(click in it once). This identifies which column contains the information you want from Spreadsheet 2.

Type the number of columns your field is from the Unique ID, where the Unique ID is 1. Here, the Email field is the third column. - Go to
**Range_lookup**(click in it once). Type**FALSE**to search for exact matches. The result will look something like this: - Finally, copy and paste the formula to pull emails for the rest of the column.

(Note: if your table array is in the same Excel workbook, put $ signs around the cell values, similar to the example below. This ensures that you reference the correct cells in the table array, meaning that the table array does not shift down when you paste the formula down. See Advanced Tip below for more details.)

**vLookup Shortcut**

- Type the beginning of the formula: =VLOOKUP(

The formula guide will appear below.

(Note: You may notice Excel displays the formula in 2 places: the formula bar above and directly in the cell. You can edit the formula in either place.) - Follow the guide and enter each value. Remember to insert a comma between each value.
- Insert a closed parenthesis ) and hit Enter. The end result will look like something like this:

=VLOOKUP(B2,'[Spreadsheet Name.xlsx]SheetName'!$B$1:$E$11,3,FALSE) - Finally, copy and paste the formula to pull emails for the rest of the column. Keep relative references in mind and use $ signs where necessary. (See Advanced Tip below for more details.)

### How Do I Merge Two Excel Spreadsheets With Common Fields

**Advanced Tip on Relative References**

- To lock in the lookup value in cell B1, insert $ signs before the column and the row:

=VLOOKUP(**$B$1**,’[Spreadsheet2.xlsx]SheetName’!$B$1:$E:$11,3,FALSE) - To lock in the column only, insert a $ before B only.
- To lock in the row only, insert a $ before 1 only.

### Merging Two Excel Spreadsheets Based On Common Field

### Merging Two Excel Spreadsheets Based On Common Field System

**Need More Information or Help?**

### Merge Spreadsheet Data

If you have questions about this Tableau document, please contact Melissa Chan, Office of Data Management and Analytics Services (ODMAS) at [email protected].Keywords: | Tableau Workbook Dashboard Excel 2 Two Combine Pull Data IDESuggest keywords | Doc ID: | 90851 |
---|---|---|---|

Owner: | Steven T. | Group: | Office of Data Management & Analytics Services KB |

Created: | 2019-04-04 11:15 CDT | Updated: | 2020-06-20 04:08 CDT |

Sites: | Office of Data Management & Analytics Services KB | ||

Feedback: | 243CommentSuggest a new document |