Savemore,
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,
Huntsman
--------------------
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
http://www.microsoft.com/office/using/column03.asp
"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:
Google
Search Microsoft-related sites using Google:
://www.google.com/microsoft.html
The search I used there was:
excel xp vlookup
://www.google.com/microsoft?q=excel+xp+lookup&hq=microsoft&btnG=Google+Search |
,
0 Comments
)