Google Answers Logo
View Question
Q: Creating Associations within MS Excel ( Answered 5 out of 5 stars,   0 Comments )
Subject: Creating Associations within MS Excel
Category: Computers > Software
Asked by: savemore-ga
List Price: $10.00
Posted: 28 Aug 2002 08:29 PDT
Expires: 27 Sep 2002 08:29 PDT
Question ID: 59447
I’m looking for help in creating associations within
"IF", "THEN" statements.  To expand a little on what I’m doing...I
currently have thousands of accounts and each one of those accounts
has a number and customer name associated with it, for example:

Account Number	Customer Name
    111111	   Doe, John
    111111	   Doe, John
    222222	   Williams, Peter
    333333         Acme Paint Co.
    333333	   Acme Paint Co.
    333333	   Acme Paint Co.
    333333	   Acme Paint Co.
    444444	   Taylor, Mary

What I would like to happen whenever I receive...say a subsequent
month’s report, is create an association (within the previous month’s
report) so that whenever I paste the Account Numbers into the old
report of the new month’s activity (off to the side of all the
existing information) Excel recognizes the account numbers and in a
separate column provides me with the customer name (as appropriate or
applicable) associated with that account number.

Using this information I would then be able to simply copy, cut and
paste this data into the new report.

Can anyone help me please?  :-)

F.Y.I.  My operating system is Windows 2000 professional (SP-3) and I
use the XP version of Excel (SP-1).  Thank you!
Subject: Re: Creating Associations within MS Excel
Answered By: huntsman-ga on 28 Aug 2002 17:05 PDT
Rated:5 out of 5 stars
You could use IF-THEN, but you will get more in the long run if you
use Excel's VLOOKUP function to do this.

Row by row, VLOOKUP checks two adjacent columns of data in any
specified range or worksheet. It looks in the first column for the
text (or value) you specify, and returns the text (or value) it finds
in the second column.

I've provided an example below, which I tested in Excel. I suggest you
try this example first to get the hang of it, then tweak things as
necessary for your specific layout.

Thank you, 


Step 1 - Master Account List

Although you can use VLOOKUP within any specified range of cells, it
works best with a clean, sorted list of data, preferably in a separate
worksheet within your active workbook. I' sure you will find other
uses for this master list as time goes on.

In your case, you should create a separate worksheet that contains
only a sorted master list of all your account numbers and names. You
can do this easily by copying and pasting your existing data into the
new worksheet. Its rows and columns should look like this:

   Worksheet Name: Account List
         A            B                C
       ------   ---------------   -----------
   1   ACCT#    NAME              WHATEVER...
   2   111111   Doe, John
   3   222222   Acme Paint
   4   333333   Williams, Peter
   5   444444   Taylor, Mary
   6   555555   HAL Corp
   7   666666   Smith, Jane
   8   777777   Coyote, Wiley
   9   etc.     etc.
Be sure to sort the Account List worksheet by column A (ACCT#) in
ascending order. You can add new rows (records) or columns (fields) at
any time, but always sort the worksheet afterwards.


STEP 2 - The VLOOKUP Function

Here is the VLOOKUP function/formula you need to use:

   =VLOOKUP(A2,'Account List'!$A:$B,2,FALSE)

Its various parts are:

  "A2" ..... refers to the cell containing the text in the first
             column of data (the Account Number). "A2" changes to
             "B2", "C2", and so on when you copy down the formula
             into your "Account Activity" worksheet below.

  "'Account List'!$A:$B" ... an absolute reference to the "Account
             List" worksheet and its columns containing the Account
             Numbers and Names.

  "2" ...... the B column in the "Account List" worksheet where 
             your result (Account Name) will come from. 

  "FALSE" .. only an exact match will give a result.

For more information on the VLOOKUP, search for it in Excel Help.

Copy the formula above, and paste it into the required rows of your
"Account Activity" worksheet using Excel's "copy down" feature.

Read on...


STEP 3 - "Account Activity" Worksheet

To test the entry of account numbers and retrieval of names, create an
"Account Activity" worksheet.

The VLOOKUP formula must be copied into all cells in the column where
you will enter (or paste) account numbers. One quick way of doing this
is to paste the formula into the first cell under the column header,
then use Excel's "copy down" feature to put it into the rest of the
column's cells. Search for "Fill in a series from a formula" in
Excel's Help for more information.

Until you start entering account numbers, the second worksheet will
look empty (the normally hidden formulas are shown below in Column B):

   Worksheet Name: Account Activity

         A            B
       ------   ---------------
   1   ACCT#    NAME
   2            =VLOOKUP(A2,'Account List'!$A:$B,2,FALSE)
   3            =VLOOKUP(A3,'Account List'!$A:$B,2,FALSE)
   4            =VLOOKUP(A4,'Account List'!$A:$B,2,FALSE)
   5            =VLOOKUP(A5,'Account List'!$A:$B,2,FALSE)
   6            =VLOOKUP(A6,'Account List'!$A:$B,2,FALSE)
   7            =VLOOKUP(A7,'Account List'!$A:$B,2,FALSE)
   8            =VLOOKUP(A8,'Account List'!$A:$B,2,FALSE)
   9   etc.     etc.


Step 3 - Enter Your Account Numbers

In the "Account Activity" worksheet, you can now type (or paste)
account numbers into Column A, and the account name will appear next
to it in Column B. A series of account names can be copied and pasted
into Column A, but there must be a matching number of cells containing
the formula in Column B for everything to work.

Depending on the order in which you enter account numbers, your final
results should look something like this:

   Worksheet Name: Account Activity

         A            B
       ------   ---------------
   1   ACCT#    NAME
   2   111111   Doe, John
   3   111111   Doe, John
   4   444444   Taylor, Mary
   5   777777   Coyote, Wiley
   6   555555   HAL Corp
   7   222222   Acme Paint
   8   333333   Williams, Peter
   9   etc.     etc.

If an exact match is not found for the account number you enter, the
text "#N/A" is inserted in Column B instead.


References - 

   Top 10 Questions About Excel
   "Q. Is it possible to have a product name enter automatically
whenever I enter a product number?"

Search Terms & Google Results - 

Did you know that Google has a search engine just for Microsoft Web
sites? See:

   Search Microsoft-related sites using Google: 

The search I used there was:

   excel xp vlookup

Clarification of Answer by huntsman-ga on 29 Aug 2002 12:22 PDT

That's great! Glad to help.

If you have any other Excel questions in the future, please don't hesitate to ask.

savemore-ga rated this answer:5 out of 5 stars
This worked like a dream and will save me hours, yes HOURS of work
every month.  One other nice feature is that it'll also provide me
with an easy to extrapolate list of new accounts as well.  Thank you

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy