Re: Excel Help
Posted 6/11/2013 6:03:28 PM by LIMomma

Vlookups work best with matching exact information (so for example if there are two Smiths this might not be your best option unless you concatenate the first and last name). If not first thing is to make sure the information is sorted alphabetically, and the same type of format (general, text)

Once you do those two steps in the column you want to match type

1. =VLOOKUP(
2. Click on the cell of the data in the spreadsheet you are in for what you want to match to the other spreadsheet...in this case last name
3. Type a comma
4. Click and highlight your data in the other sheet. This should be ALL the data that you want to bring over to the other spreadsheet. click from the first last name across to the last field that you want to bring over and all the way down to the last line.
5. Press F4 until you see dollar signs in front of your selection for both the column and the row. This keeps the selection from moving when you use it for the other columns you want to bring over
6. type a comma
7. Type the NUMBER of columns over from where you started for the information you want to bring back. For example on your selection if A is last name and C is street address type 3 (Excel can not calculate Letters - you have to change it to a number)
8. Type a comma
9. I recommend that you type false so that you only get exact matches (you can leave this out if you want to find the closest one)
10. Type a close parenthesis

you can copy and past this formula by highlighting the formula and copy and pasting it in all the other cells, then simply change the 3 after the comma to 4 or 5 or 6 depending on if you have a city column, state column, zip column.

You should also make sure there is no weird spacing either after the text or with certain names that would stop the data from coming over.

Hope this helps

PS -- The other way to do this (I do this when I have small amounts of data)

Highlight the data with the addresses in a color, copy and paste it into the other sheet. Sort in ascending order by last name -- you will see the ones that have addresses and you can delete the other rows.....

FM me with any questions if you want.
Print Post      Close Window