Complex lookup or index matching?

[Evil8]

Things just got more complicated...

I need to pull the ContactID from a Contact workbook that matches the names in the Policy workbook. I'll be using the Policy workbook to upload data to a database.

The Policy workbook has column F (Contact First name) and column G (Contact Last Name)
The Contact workbook has column E (LASTNAME) and column F (FIRSTNAME). The ContactID is Column A.

Any help here would be great!

Thanks

 

[kjv1611]

A couple of initial questions:
1. Why are you using a ContactID, but yet you are pulling in FirstName LastName instead into the Policy workbook?
2. Are these Contacts and Policies in a 1:1 ratio? I'm guessing sometimes yes, but there is a possibility for a one to many, or in some circumstances, a many-to-many relationship.
3. When you say you import the data into a database, what do you do with it there??
4. From what you've told us, it sounds like the Workbook is acting as the datasource, but Access as the analysis tool, perhaps? That seems backwards to me. Excel was built for analysis, and Access was built to store data, at their core.

If possible, my initial suggestion is to make sure you're "data store" is actually the database, not the spreadsheet.
Then, build whatever queries you need to match things up in the database.
Then from within Excel, import the data from the datbase, and show it in any fashion you need - emailed list, summarize with a pivot table, etc.

If you must keep it in Excel, I'd also suggest something else (Assuming you're using Excel 2007 or 2010): Use tables within your worksheets. Then you can simply name the tables just like you would in Access, and you can reference the tables and columns rather than A1 and G2 for instance.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

hi,

What data do you have in your new workbook for lookup?

Which column does that correspond to in the Policy workbook?

Can the Policy Workbook be open during the update?

Does the Policy workbook have Heading values in Row1?

Please answer each and every question.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Evil8]

Hey guys thanks for your help. I'll try to answer all the questions.

I exported the Couple of thousand Contacts into a Contact worksheet from the new online database system. It has the unique CONTACTID (A) (created when I uploaded the contacts this morning), LASTNAME (E) and FIRSTNAME (F). I do have a NAME column (H) that was created by the new system but that is just first and last name put together already.

I need to upload Policies using a data loader provided by the software company. In that workbook I have CONTACTFIRSTNAME(E) and CONTACTLASTNAME (F). I need the ContactID from the Contacts worksheet.

I currently have both workbooks open. They both have column headers in row 1, not data values.

In this particular case I there is only 1 policy for 1 contact, but matching may be an issue for a few so I expect some errors. For example I have 5 different Donald Johnson IDs. I expect I'll have to look those up by hand. The Contact worksheet has 2830 rows, the Policy has 2647 rows.

Thanks.

 

[SkipVought]

Have you constructed formulas before, using references by either selecting a cell or a range, like an entire column? You should be able to construct your INDEX & MATCH formula with a minimum of typing; certainly NO TYPING for any reference!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Evil8]

Sorry I've been doing some other issues that have been put off while I worked with the data. I'm back to this task.

I've just started using vlookups so this is all a bit different. Now on both sheets I've concatenated the Last, First and Middle Name column values into one Column H and done a VLOOKUP on that column. I have what I need now, but it took extra steps. Thanks.