This post builds upon “How to merge data sets using an Excel or Calc spreadsheet.” A reader, Tina, asked “What if you have more than one column to lookup to get the match value?” This post tries to answer that question.
The key thing about merging data sets is that they MUST share at least one column in common. If this is not the case, then you will have to make it so!
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | First Name | Last Name | Address | Name | Phone | |||
2 | Sly | Stone | 123 Main St | George Clinton | 555-555-6969 | |||
3 | George | Clinton | 1 P-Funk Ln | Sly Stone | 555-555-1212 | |||
4 | Stevie | Wonder | 22 Talking Book Ln | Stevie Wonder | 555-555-2323 |
The solution
Create a new column that takes the data from the two columns and combines them into one.
Step-by-step
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | First Name | Last Name | Name | Address | Name | Phone | ||
2 | Sly | Stone | Sly Stone | 123 Main St | George Clinton | 555-555-6969 | ||
3 | George | Clinton | George Clinton | 1 P-Funk Ln | Sly Stone | 555-555-1212 | ||
4 | Stevie | Wonder | Stevie Wonder | 22 Talking Book Ln | Stevie Wonder | 555-555-2323 |
Cell C2 contains the following formula: =a2&” “&b2 which contains three parts.
- =a2 refers to a cell containing a first name.
- &” “& represents the addition of some text. What we are adding is enclosed and separated from the rest of the formula by a pair of & symbols. The text being added is enclosed by a pair of quotation marks. In this example, the text being added consists of a single space.
- b2 this is a cell reference, in this case it is a cell containing a last name.
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | First Name | Last Name | Name | Address | Phone Lookup | Name | Phone | |
2 | Sly | Stone | =a2&” “&b2 | 123 Main St | =vlookup(c2,g$2:h$4,2,0) | George Clinton | 555-555-6969 | |
3 | George | Clinton | George Clinton | 1 P-Funk Ln | 555-555-6969 | Sly Stone | 555-555-1212 | |
4 | Stevie | Wonder | Stevie Wonder | 22 Talking Book Ln | 555-555-2323 | Stevie Wonder | 555-555-2323 |
Dollar signs
If unsure about the dollar signs, check out this earlier post. Basically, using dollar signs will control for unwanted shifting when copying and pasting the formula.
Paste special
If unsure about the use of paste special, check out this earlier post. Basically, your newly-merged data set relies on being able to look up other cells. If you delete those cells, the newly-merged data set will be affected.
Was this exactly what you needed? Did I leave something out? What other spreadsheet how-to’s do you want to see?
This helpful – I usually do this long process of copying the fields into a Word doc, converting table to text, then back text to table, and copying back in to excel.