Excel - joining data
How to join data in Excel (when you don't have Access)
Using Microsoft Access is a quick way to join data, but many people don't have Access. And it's not even offered for Apple users. So here's the workaround using the VLookup function in Excel.
Example
Let's assume you have a list of towns (Column A) and states (Column B), but the states are listed by the two-letter postal abbreviations. You want to change the abbreviations to the full state names without having to search and replace 50 times for 50 different states.
Step 1
- Find a list somewhere on the Internet (or your computer) that matches the postal abbreviations to full state names.
Step 2
- Copy this list into columns E and F. For our example, we'll assume the postal abbreviations are in Column E and the full state names are in Column F. This array will run from row 2 through row 51. It's important to take note of this range (E2:F51).
Step 3
Type in this formula in cell C2, next to the first set of values
- =VLOOKUP(B2,D$2:E$51,2,0)
Step 4
- Drag to repeat the formula down through the end of your unique values.
Explaining the formula
- B2 — The item from the original list you want to join to the array.
- D$2:E$51 — The array being used for joining purposes. The dollar signs are used to freeze those numbers as you repeat the formula going down.
- 2 — This tells the formula which column from the array you want to display.
- 0 — This signifies that you are looking for exact matches. If you instead use "1" or leave this blank, you'll get close matches (not good).
