Let us imagine two related data sets in Excel or LibreOffice’s Calc. One contains a list of names and corresponding phone numbers, and the other is a list of names and email addresses. The two data sets have a unique id in common (like a Social Security number). We want to merge the two data sets together into a single table. This is a common problem with an easy solution.
|2||01||555-1313||Dennis Osorio||06||Jesse Pinkmanemail@example.com|
|3||02||555-4242||Carrie Mathison||09||Tom Haverfordfirstname.lastname@example.org|
|4||03||555-2323||Saul Berenson||05||Walter Whiteemail@example.com|
|5||04||555-5353||King Joffrey||01||Dennis Osoriofirstname.lastname@example.org|
|6||05||555-3636||Walter White||10||Andy Dwyeremail@example.com|
|7||06||555-6464||Jesse Pinkman||07||Hank Schraderfirstname.lastname@example.org|
|8||07||555-4747||Hank Schrader||02||Carrie Mathisonemail@example.com|
|9||08||555-7373||Saul Goodman||08||Saul Goodmanfirstname.lastname@example.org|
|10||09||555-5858||Tom Haverford||02||Saul Berensonemail@example.com|
|11||10||555-8484||Andy Dwyer||04||King Joffreyfirstname.lastname@example.org|
Vlookup is used by both Excel and LibreOffice’s Calc.
|1||ID||Tel Number||Name||Email Lookup||ID||Name|
|2||01||555-1313||Dennis Osorio||=vlookup(a2,$f$2:$h$11,3,0)||06||Jesse Pinkmanemail@example.com|
|3||02||555-4242||Carrie Mathison||=vlookup(a3,$f$2:$h$11,3,0)||09||Tom Haverfordfirstname.lastname@example.org|
|4||03||555-2323||Saul Berenson||=vlookup(a4,$f$2:$h$11,3,0)||05||Walter Whiteemail@example.com|
|5||04||555-5353||King Joffrey||=vlookup(a5,$f$2:$h$11,3,0)||01||Dennis Osoriofirstname.lastname@example.org|
|6||05||555-3636||Walter White||=vlookup(a6,$f$2:$h$11,3,0)||10||Andy Dwyeremail@example.com|
|7||06||555-6464||Jesse Pinkman||=vlookup(a7,$f$2:$h$11,3,0)||07||Hank Schraderfirstname.lastname@example.org|
|8||07||555-4747||Hank Schrader||=vlookup(a8,$f$2:$h$11,3,0)||02||Carrie Mathisonemail@example.com|
|9||08||555-7373||Saul Goodman||=vlookup(a9,$f$2:$h$11,3,0)||08||Saul Goodmanfirstname.lastname@example.org|
|10||09||555-5858||Tom Haverford||=vlookup(a10,$f$2:$h$11,3,0)||02||Saul Berensonemail@example.com|
|11||10||555-8484||Andy Dwyer||=vlookup(a11,$f$2:$h$11,3,0)||04||King Joffreyfirstname.lastname@example.org|
Parameter-1 should refer to a cell. In our example, that cell is A2. The formula will try to find the contents of A2 elsewhere – like within another data set.
Parameter-2 should refer to a range of cells that represent the “somewhere else” in question. In our example, the range is F2:H11. So far, the formula in the example looks like =vlookup(a2,f2:h11,parameter-3,parameter-4) which indicates that the cell A2 is the value which will be looked up in the range F2:H11.
Parameter-3 is a number that represents [X number of columns] from the initial match. It is contains the value that will be returned upon successful completion of this formula. So far, the formula in the example looks like =vlookup(a2,f2:h11,3,parameter-4) which indicates that the cell A2 is the value which will be looked up in the range F2:H11. And, when a match is found, the value from the cell 2 columns to the right will be returned to me.
- 1 would be the same column where the match is found
- 2 would be one column to the right
- 3 is two columns to the right.
Parameter-4 only accepts a few possibilities: 0, 1, false, or true. This refers to whether fuzzy matches are acceptable, which would depend on (for starters) whether the first column in the cell reference specified in parameter-2 was sorted in ascending order. In the example, the formula is now =vlookup(a2,f2:h11,3,0). I entered 0 or false for parameter-4 because I want an exact match.
What about the dollar signs?
In the example above, when entering certain cell references, I also included dollar signs ($). It is to control shifting when copying and pasting formulas. If you do not know what I mean, then prepare to unlock The Mystery of Dollar Signs in your Spreadsheet, or How Do Cells Find Each Other?
If I were to delete columns f:h, the Email Lookup column would change, and I would get a bunch of #N/A values. I would have broken 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 newly created column.
- 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.
I Need Feedback
Did this tutorial work? Was it exactly what you needed? Was it confusing? Did I leave something out?
Spread the love! Ask questions, leave a comment, or simply share this with someone you know would really benefit. What other spreadsheet tutorials would you like to see?
If you find yourself in a pinch, you should know that I am available to help with spreadsheet projects.