Solved

Lookup table based on last name and street address

Posted on 2014-01-29
3
268 Views
Last Modified: 2014-01-31
Hello

I put together a spreadsheet with names and addresses and other things.

At some place down the road I accidentally deleted the column with the FIRST NAME of the person.

So what I want to do is to restore the first name of the person based on the earlier file I have.

I want to base the first name on two criteria:

1) The last name and street address on Sheet   I need both to be 100 percent sure that I am associating the correct last name with the restored first name.

2)  Sheet two contains the first name, last name street address and other data.  It is in a different order and also contains names that are not on Sheet 1.  Also the columns are different in sheet 2 then sheet 1.  But I figure a lookup table would have no trouble taking care of fixing my missing first name goof.

That's why I am looking at a lookup solution to solve this.

I have about 2 thousand names and addresses where I need to restore the first name.   The attached file has only a few names, but that will be enough to figure out the lookup formula.

I think my question is pretty straight forward. If you could look at the attached file and show me how to do the lookup table -- I will transfer it to my real file.  I modified the names and addresses for privacy purposes.

Thanks!

ROwby
Experts-exchange-first-name-miss.xlsx
0
Comment
Question by:Rowby Goren
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39819633
rowby,

Please look at the green columns I have added in. One of them is the lookup column using Index Match, and the other 2 (one on each tab called Helper Column is to help to to make sure you are matching the right record.

For some of your record, there maybe inconsistent address line which will cause N/A error. In your sample, there is a problem with 1 space and 2 spaces in the address line.

You just have to go through those one by one. At least, this will take care of at least 80% of them.
Experts-exchange-first-name-miss.xlsx
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39819914
Thanks, Harry Lee

I will double check for "double spaces".

I am away but will look at it first thing in am.
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 39825097
Thanks Harry Lee,

It worked perfectly!   I wish I could give you 1000 points!

Rowby
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question