Using Vlookup in Excel
Submitted by LindyWalsh on June 7, 2010 - 11:13am.
I download data from our database and it downloads as two tables which I can save as Excel worksheets. One table has the demographic info (name, address, etc). The other table might have our "contact" info (attendance at an event, letter we may have sent them, etc). I'm wanting to manipulate the data. In the past I've sent it into access but I thought it might be easier in excel.
The data in each table has a unique link field for each individual.
Someone mentioned that I could use VLookup as a way to get information from both tables.
I haven't a clue how to do it. Any advice?
Thanks
Lindy
- Login to post comments
How to Use VLOOKUP
VLOOKUP is kind of like a complex "Find and Replace" formula. One thing we've used it for is for converting a list of abbreviated state names to the full state names (when dealing with address data while importing, we needed all the states listed in their full form, NOT the abbreviated form).
I've attached a spreadsheet with this formula in it to the Intermediate/Advanced Excel 2007 for Windows training page. You'll need to download the spreadsheet so that you can refer to it while reading this explanation.
In order to use this formula, you need 4 cells: the original data cell, the cell the formula will live in, the cell with the long state name, and the cell with the short state name that it should be turned into. I’ve applied the formula to a whole column here.
If you click in the first cell in column N (not the heading label), you should see this: =VLOOKUP(A2, $C$2:$D$60, 2, FALSE)
Let's look at each argument in the formula.
A2 is the cell containing the original data, i.e. the string “CA”.
The long argument with dollar signs ($C$2:$D$60) is the range of cells that the formula tells Excel to look through to find 2 things: 1) a string matching the original data, in column C and 2) the string it should be turned into, in column D, in this case, “California”.
2 is the column you want your data to end up as (putting 1 would mean you’d your data to end up looking like the C column, and the proper state name would turn into the abbreviation).
And finally, FALSE is what I put if I want the match to be exact. If I was okay with a close match, like “Wisconson”, I would put TRUE. But we don’t want an approximate match, so I choose FALSE for an exact match.
This is a great formula, and my recommendation is that you keep the example formula attached here on hand to copy and paste when you need it. Then you can just update the cell references, and it will be much quicker than trying to get it just right yourself. Believe me! It's easy to spend way too much time trying to remember all the different pieces of the VLOOKUP formula.
If you can give more information about exactly what data manipulation you want to do, I may be able to give additional suggestions.