Solved

Lookup table based on last name and street address

Posted on 2014-01-29
3
260 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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

810 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