?
Solved

Lookup table based on last name and street address

Posted on 2014-01-29
3
Medium Priority
?
296 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 2000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

589 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