I think I'm going to end up with two spreadsheets for customer tracking info. One spreadsheet will have about 8,000 lines. The other will have about 10,000 lines. The common element between the two (Key) is the customer numbers. How do I combine the data into one spreadsheet where information about one customer in the shorter spreadsheet will show up in the same customer number line in the long spreadsheet?

Hi Brazzell Marketing,

Does the longer workbook hold all the records that appear in the shorter one? If so, it's probably just a case of using a lookup formula to synchronize data between the two.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes, the longer worksheet has all the threads of the shorter one and more. Can you point to a good tutorial on lookup formulas in Excel?

Hi Brazzell Marketing,

I'm not aware of any tutorials, but the lookup functions are not difficult to master. However, they do require the lookup data (in this case, the column in the smaller workbook that is used to reference the data) to be sorted. If the data are not sorted, a different approach is required.

The attached workbook has data in columns A & B, some 'lookup' values in D and two different formulae for doing the lookups in E and F. As you will see, the LOOKUP formula in E makes false positive matches when the 'lookup' values in D aren't found in column A. The INDEX/MATCH pair in column F don't do that (they return a #N/A result instead); they also work with unsorted data.

Attached Files

Lookup.xls (19.5 KB, 20 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks. Aparently I repped you too recently to rep you again. Where's the link to mark this thread as solved?

It's under 'Thread Tools' at the top of the thread. Done.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]