Here is the scenario
You have two related datasets in Excel or OpenOffice’s Calc. One is a list of names with phone numbers, and the other is a list of names and email addresses. The two datasets have a unique id in common (like a Social Security number). You want to merge the two of them together. This is a common problem with an easy solution.
Sample
Using the picture to illustrate the problem, on the left-hand side is a list of names and phone numbers. On the right, a list of names and email addresses. The goal is to have one list, not two.
The solution
Vlookup – used by both Excel and OpenOffice’s Calc.
Vlookup: step-by-step
- =vlookup(a2 – “a2″ refers to what is being looked up. Meaning look at the cell, a2, and find it somewhere else – like within the other dataset.
- =vlookup(a2,g1:i11, – “g1:i11″ is the range, that is, the “somewhere else” in question. A2 is the value being looked up in the range g1:i11. Note: Excel uses commas between each parameter, whereas Calc uses a semi-colon. Otherwise, the formula is identical.
- =vlookup(a2,g1:i11,3, – When a match is found, what next? “3″ means: give me the value from the cell 2 columns over from where you found the match.
- 1 would be the column where the match is found
- 2 would be one column to the right
- 3 is two columns to the right.
- =vlookup(a2,g1:i11,3,0) – “0″ refers to whether you want a fuzzy match. False or zero is the answer. I want an exact match.
What is with the dollar signs?
I used dollar signs in the images above. The dollar signs tend to confuse people. When you copy the formula from one cell to another, the spreadsheet (being so smart) shifts everything relatively.
Some of this shifting you want, some of it you don’t. I do want it to shift from a2 to a3 to a4, but I want the range where it is finding these matches to stay exactly the same.
To control for this, you add dollar signs to indicate no shifting. There is a dollar sign in front of the a in $a2, because I want column a to stay consistent no matter where I copy and paste this formula. The “2″ can change, but not the “a.”
Similarly I indicated “$g$1:$i$11″ – this is the same as g1:i11, except that I’m specifying that I don’t want any shifting to happen if I copy and paste the formula. Neither column nor row shifting is allowed.
Paste Special
Finally, if you go and delete columns g:i at this point, the email lookup column will change, and you’ll get a bunch of “#N/A” values. This is because you just broke the reference – the formula needs to be able to refer to the other cells.
To correct for this, what I would do is
- Insert a new column next to “email lookup.”
- Highlight and copy “email lookup.”
- Select the column that you just created.
- Select “paste as values” – if you don’t know what this means, find the edit pull-down menu, and select “paste special.” There is a choice there for paste values – meaning it will paste only the text (or number). No formatting, no formulas – just the values.
Was this exactly what you needed? Did this how-to suck? Did I leave something out? What other spreadsheet how-to’s do you want to see?
7 Comments
What if you have more than one column to lookup to get the match value?
Tina: It sounds like you have two datasets. One dataset has two columns (like ‘First Name’ and ‘Last Name’). The other has the information in one column (like ‘name’). And, you want to merge the two datasets. The first step would be to combine the two columns of the first dataset into one column, so that it matches up with the other dataset. I created a new post to illustrate. See http://dposorio.com/wordpress/archives/221.
Thanks, I wasn’t aware of vlookup. I assume you could do the same thing with multiple sheets by simply referring to the sheet name?
Leroy: yes, that’s exactly how it works. You can simply mouse your way to another sheet for the vlookup. Notice how if your sheet name has a space in it, it is enclosed within apostrophes/single quotes. I have found it easy to break these references from one worksheet to another, so I try to minimize it whenever I can.
I want to no use of vlookup in data entry for correction on data
one real sheet and another throughout that new sheet which willl be made
so how will vlook up used on new sheet with there old refernece…………
Brilliant. Exactly what I was looking for. Thank you so much. Incindentally, I believe that to paste values in Calc you have to select “text” in the paste special box. A marginally shorter way is to click the little tab next to the paste icon and select “unformatted text” and press enter.
Thanks for the feedback, Nat!