• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Lookup table based on last name and street address

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
Rowby Goren
Asked:
Rowby Goren
  • 2
1 Solution
 
Harry LeeCommented:
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
 
Rowby GorenAuthor Commented:
Thanks, Harry Lee

I will double check for "double spaces".

I am away but will look at it first thing in am.
0
 
Rowby GorenAuthor Commented:
Thanks Harry Lee,

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

Rowby
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now