VLookup (I Think) Help Please

[jdogg]

Hello all, long time lurker...

Normally I can find the answer to any excel problem I have in these forums, but I can't seem to manage this time.

I have an email database in Excel. There are 2 sheets. The first sheet is called 'Cleansed', and contains 1000 active users. Fields are FirstName (A1), LastName (B1), Email (C1).

In my second sheet, called 'Full', I have a much larger email database, from the time before it was cleansed (resulting in the first sheet). As well as containing the same 3 fields described above, it contains further information, such as DoB (D1), Postcode (E1), Gender (F1).

Now I need to email some users from the cleansed database, but based on information such as DoB from sheet 2. I cannot just use sheet 2, as it will contain users who have since unsubscribed, or who are junk/invalid.

I was thinking it would be a VLookup I would use, but how would I do this to firstly match names (or probably email addresses as they would be more unique) from the final Full list to the Cleansed list, and then pull through the additional information (DoB, Gender) into the correct users on the cleansed list.

Sorry if that is confusing...

Jason

 

[VoG]

Try

DoB =VLOOKUP(C1,Full!C1:F10000,2,FALSE)

Postcode =VLOOKUP(C1,Full!C1:F10000,3,FALSE)

Gender =VLOOKUP(C1,Full!C1:F10000,4,FALSE)

 

[obiron]

@VoG - That will simply return a value if it exists, it will not ensure that the three values come from the same row.

@jdogg - Welcome to the world of the database. As you have discovered to your cost it is often necessary to have a unique key on your table that will enable you to match records from one worksheet (table) to another.

There are more elegant but long winded ways of doing it but probably the most pragmatic is to create a new column in the FULL sheet which is a concatenation of the first name, surname and email address and do the same of the cleaned sheet. You will need to make this column the first one in the FULL sheet and then you can do VLOOKUP on that combined column.

Obiron