Solved

Lookup table based on last name and street address

Posted on 2014-01-29
3
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

636 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